The OPNQRYF (Open Query File) command on IBM i (AS/400) is used to dynamically query physical or logical files. It allows you to apply selection criteria, sorting, and other options without needing to create a permanent logical file.

💡 Basic Syntax:

OPNQRYF FILE((MYFILE)) QRYSLT('FIELD1 *EQ "VALUE"') KEYFLD((FIELD2))

🧱 Common Parameters:

• FILE: Name(s) of the file(s) to query.

• QRYSLT: Selection criteria (like a SQL WHERE clause).

• KEYFLD: Sort fields (like a SQL ORDER BY).

• OPNID: Optional ID if you're using multiple files.

• FORMAT: File format name (useful when field names differ).

• MAPFLD: Allows defining or renaming fields on the fly.

📌 Simple Example:

OPNQRYF FILE((CUSTOMERS)) QRYSLT('CITY *EQ "PARIS"') KEYFLD((NAME))

This selects all customers from Paris and sorts them by the NAME field.

📌 Typical Usage Pattern:

1. Use OPNQRYF to filter and sort data.

2. Call a RPG program that reads the file.

3. Use CLOF to close the file after processing.

OPNQRYF FILE((CUSTOMERS)) QRYSLT('CITY *EQ "PARIS"')
CALL PGM(MYPROGRAM)
CLOF OPNID(CUSTOMERS)

Here’s a simple RPGLE example that works with a file opened using OPNQRYF.

🔧 1. CLLE Program Using OPNQRYF

PGM

DCLF FILE(CUSTOMERS)

OPNQRYF FILE((CUSTOMERS)) +
         QRYSLT('CITY *EQ "PARIS"') +
         KEYFLD((NAME))

CALL PGM(PROCUST)

CLOF OPNID(CUSTOMERS)

ENDPGM

This CL program:

• Opens the CUSTOMERS file using a filter (CITY = 'PARIS')

• Sorts the output by the NAME field

• Calls an RPGLE program PROCUST

• Closes the file after processing

🧾 2. RPGLE Program (PROCUST)

**FREE

DCL-F CUSTOMERS USAGE(*INPUT);

DCL-S FullName VARCHAR(50);
DCL-S City     VARCHAR(20);

DOW '1';
   READ CUSTOMERS;
   IF %EOF(CUSTOMERS);
      LEAVE;
   ENDIF;

   FullName = %TRIM(NAME);
   City = %TRIM(CITY);

   Dsply ('Customer: ' + FullName + ', City: ' + City);
ENDDO;

*INLR = *ON;
RETURN;

What this does:

• Reads from CUSTOMERS, which is already filtered and sorted by OPNQRYF.

• Displays each customer name and city on the screen.

Here's an advanced example using OPNQRYF with multiple files and a join, plus an RPGLE program that reads the joined data. This setup simulates a real-world case: displaying customer orders by joining CUSTOMERS and ORDERS.

🔧 1. Files Assumed

• CUSTOMERS: Contains CUSTID, NAME, CITY

• ORDERS: Contains ORDERID, CUSTID, ORDERDATE, TOTAL

🧩 2. OPNQRYF in CLLE (Join + Selection + Sort)

PGM

DCLF FILE(CUSTORDJOIN)

OPNQRYF FILE((CUSTOMERS) (ORDERS)) +
         FORMAT(CUSTORDJOIN) +
         QRYSLT('CUSTOMERS.CUSTID *EQ ORDERS.CUSTID *AND CITY *EQ "PARIS"') +
         KEYFLD((CUSTOMERS.NAME)) +
         JFLD((CUSTOMERS.CUSTID ORDERS.CUSTID)) +
         MAPFLD((NAME CITY ORDERID ORDERDATE TOTAL))

CALL PGM(PROCUSTORD)

CLOF OPNID(CUSTOMERS)
CLOF OPNID(ORDERS)

ENDPGM

🔍 Breakdown:

• JFLD: Defines the join between CUSTOMERS.CUSTID and ORDERS.CUSTID

• QRYSLT: Filters for customers in Paris

• KEYFLD: Sorts by customer name

• MAPFLD: Passes the desired fields

⚠️ You must define a join logical file format (CUSTORDJOIN) or use MAPFLD to define a custom format for fields.

💻 3. RPGLE Program PROCUSTORD

**FREE

DCL-F CUSTORDJOIN USAGE(*INPUT);

DCL-S Name      VARCHAR(50);
DCL-S City      VARCHAR(20);
DCL-S OrderID   PACKED(7:0);
DCL-S OrderDate DATE;
DCL-S Total     PACKED(9:2);

DOW '1';
   READ CUSTORDJOIN;
   IF %EOF(); LEAVE; ENDIF;

   Name = %TRIM(NAME);
   City = %TRIM(CITY);

   Dsply ('Customer: ' + Name + ' (' + City + ')');
   Dsply ('Order ID: ' + %CHAR(ORDERID) + ', Date: ' + %CHAR(ORDERDATE));
   Dsply ('Total: $' + %CHAR(TOTAL));
ENDDO;

*INLR = *ON;
RETURN;

Would you like me to generate the DDS for CUSTORDJOIN or suggest how to replace this approach using embedded SQL JOIN in modern RPGLE instead of OPNQRYF?