In Cool:2E (also known as Synon/2E) on the IBM i (AS/400), a Query Access Path (QRY access path) is a type of access path used to define how records in a file should be selected and ordered for retrieval — especially for reports, displays, or program logic that requires data sorted or filtered in a specific way.

🧠 What Is a Query Access Path?

A Query Access Path:

• Is a temporary access path created at runtime, unlike physical or keyed logical access paths which are stored and maintained on disk.

• Allows dynamic selection criteria and ordering.

• Is used in functions such as:

  • RTVOBJ (Retrieve Object)

  • DSPRCD (Display Record)

  • PRTRCD (Print Record)

🔄 How It Works in Synon/Cool:2E

When defining a file access in 2E:

• You can choose an access path type: Physical, Logical (Keyed), or Query.

• For Query, you define:

  • Select fields: Conditions on which records to include

  • Sort fields: How to order the records

  • Optionally, subset logic (like “Top N” records)

At runtime, the system builds this access path temporarily, processes the request, and then discards it.

📘 Example Scenario

You define a DSPRCD (Display Record) function on a file ORDERS.
In the access path used for this function:

• You define it as a Query access path

• You specify:

  • Select where status = 'OPEN'

  • Sort by ORDER_DATE descending

At runtime, Synon generates RPG/COBOL code that will:

• Use a dynamic query (via OPNQRYF, embedded SQL, etc.)

• Retrieve only OPEN orders

• Sorted by date

✅ Advantages

• Flexible

• No need to maintain permanent logical files

• Optimized for runtime performance (especially if data changes frequently)

⚠️ Considerations

• Performance may be lower than permanent keyed access paths if used very frequently

• Not suitable for high-volume, repetitive access (better to use keyed logicals for that)

Here’s a step-by-step example of how to define a Query Access Path in a function using Cool:2E (Synon/2E) on IBM i:

🔧 Example Scenario:

We want to create a Display Record (DSPRCD) function to show all "OPEN" orders, sorted by order date descending.

📘 Step-by-Step in 2E:

1. Create or Select the File

• Go to Work with Files (YWRKFIL).

• Choose or create the ORDERS file.

2. Define a Function

• Create a new DSPRCD (Display Record) function for the ORDERS file.

• Example: Function name: DSPORDERS

3. Define the Access Path

• Go to the function details: YEDTFUN → select your function.

• Navigate to Access Path: use option 2=Edit access path.

4. Change Access Path Type to 'QRY'

• On the Access Path screen:

  • Set Access Path Type to QRY.

5. Define Select Logic

• Use option 5=Edit access path logic.

• Go to the Select Fields section.

• Add a condition:

  • Field: STATUS

  • Operator: EQ

  • Value: 'OPEN'

6. Define Sort Logic

• Go to the Sort Fields section (still in access path logic).

• Add:

  • Field: ORDER_DATE

  • Order: D (Descending)

7. Generate and Compile

• After saving the access path logic:

  • Return to the main menu.

  • Run generation: YGEN, YBLD, and YCOMPILE as needed.

• Synon will generate the appropriate code, likely using OPNQRYF or SQL, to:

  • Filter the orders (STATUS = 'OPEN')

  • Sort by ORDER_DATE DESC

✅ Result

When the user runs the DSPORDERS function:

• Only records with STATUS = 'OPEN' are shown

• Ordered by ORDER_DATE descending

• No permanent logical file is created — the access path is built dynamically at runtime

Here’s a simplified RPG equivalent of what Synon/2E might generate under the hood when using a Query Access Path with select and sort logic.

🎯 Assumptions

• File: ORDERS

• Fields:

  • STATUS (e.g., CHAR(10))

  • ORDER_DATE (e.g., DATE)

• Selection: STATUS = 'OPEN'

• Sorting: ORDER_DATE DESC

🧾 RPG Equivalent Using OPNQRYF (Classic Technique)

FORDERS    IF   E           K DISK    USROPN

DCLF ORDERS

C* Open the file with query access path
C          CALL      'QCMDEXC'
C          PARM                    CMD           256
C          PARM      256          CMDLEN

C* Define the command to open query
C* Select where STATUS = 'OPEN' and sort descending by ORDER_DATE
C* Make sure the path is to the file name defined in F-spec

 /FREE
   CMD = 'OPNQRYF FILE((ORDERS)) ' +
         'QRYSLT(''STATUS *EQ "OPEN"'') ' +
         'KEYFLD((ORDER_DATE *DESCEND)) ' +
         'OPNID(ORDERS)';
   CMDLEN = %LEN(%TRIM(CMD));
   CALLP QCMDEXC(CMD: CMDLEN);

   // Now read from the file as usual
   READ ORDERS;
   DOW NOT %EOF(ORDERS);
      // process record
      READ ORDERS;
   ENDDO;

   // Close the file
   CLOSE ORDERS;
 /END-FREE

💡 Notes:

• QCMDEXC is used to run OPNQRYF from RPG.

• The file is marked USROPN in the F-spec, so you control the open/close manually.

• KEYFLD((ORDER_DATE *DESCEND)) defines the descending sort.

• QRYSLT filters records to only those where STATUS = 'OPEN'.

🧠 Modern Alternative (Embedded SQL in RPG Free)

dcl-s status char(10);
exec sql
   declare c1 cursor for
   select * from ORDERS
   where STATUS = 'OPEN'
   order by ORDER_DATE desc;

exec sql open c1;
dow sqlcode = 0;
   exec sql fetch c1 into :status, ...;
   if sqlcode = 0;
      // process record
   endif;
enddo;
exec sql close c1;