Developer ToolsFree referencePDF, Excel, Word exportIBM iAll IBM i (AS/400) ERPs tools

Query/400 (WRKQRY) Record Selection Tests and Result Field Functions

A searchable reference for Query for IBM i (Query/400, WRKQRY and RUNQRY): every record selection test with its exact syntax, the functions you can use in result fields, and recipes that solve the usual ERP date and selection problems. Everything here matches IBM's Query for IBM i Use guide.

Download:

Test or function	Where	Syntax as typed	Meaning	Example	Gotcha
EQ	Select records	FIELD EQ value	Equal	STATE EQ 'TX'	Character constants need apostrophes; field and value are compared character by character, shorter one padded with blanks.
NE	Select records	FIELD NE value	Not equal	NAME NE 'SMITH'	SMITHSON and Smith are both selected: NE is an exact match test.
GT	Select records	FIELD GT value	Greater than	BALDUE GT 1000	For character fields the collating sequence decides order (hex by default: lowercase sorts before uppercase, letters before digits).
GE	Select records	FIELD GE value	Greater than or equal	INTRAT GE 18	The value can be another field name, e.g. BALDUE GE CRLIMIT.
LT	Select records	FIELD LT value	Less than	BALDUE LT CRLIMIT	Field to field comparisons need compatible types.
LE	Select records	FIELD LE value	Less than or equal	QTYOH LE 0	Numeric constants: no thousands separators.
RANGE	Select records	FIELD RANGE low high	Between two values, inclusive at both ends	ORDDT RANGE 20260101 20261231	Two values separated by a blank. If low is greater than high nothing is selected.
LIST	Select records	FIELD LIST v1 v2 v3	Equal to any value in the list	STATE LIST 'TX' 'OK' 'LA'	Values separated by blanks, no field names. Each character value in its own apostrophes.
NLIST	Select records	FIELD NLIST v1 v2	Not equal to any value in the list	CODE NLIST 'X' 'Y'	Same rules as LIST. If the list wraps onto the next Value line, keep a blank before the next value.
LIKE	Select records	FIELD LIKE 'pattern'	Matches a pattern; % = zero or more characters, _ = exactly one	CUSNAM LIKE '%SMITH%'	Characters match exactly, so case matters. '%A' fails on a padded field because the last position is a blank: use '%A%'.
NLIKE	Select records	FIELD NLIKE 'pattern'	Does not match the pattern	PARTNO NLIKE 'OBS%'	Character fields only. For a date field, build a result field with CHAR() and test that.
IS	Select records	FIELD IS NULL	Field is null	SHIPDT IS NULL	The only valid value is NULL. Only meaningful on null-capable fields (common with SQL-created tables and outer joins).
ISNOT	Select records	FIELD ISNOT NULL	Field is not null	NAME ISNOT NULL	Typed as one word, ISNOT.
AND / OR	Select records	AND/OR column	Blank means AND. OR starts a new group; a record is selected if any group is fully true	STATE EQ 'TX' AND BALDUE GT 0, OR STATE EQ 'OK'	ANDs bind tighter than ORs: repeat a shared condition in every OR group if it must apply to all.
+ - * /	Define result fields	FIELD1 * FIELD2	Arithmetic on numeric fields and constants	AMOUNT = QTY * PRICE	Set Len and Dec on the result field or you may get truncation or rounding you did not want.
\|\| (concatenation)	Define result fields	A \|\| B	Joins character values	FULLNM = FIRSTN \|\| ' ' \|\| LASTN	Fixed-length fields keep trailing blanks inside the result.
SUBSTR	Define result fields	SUBSTR(value, offset, length)	Part of a character value; length optional	SUBSTR(ALPHA,4,3) gives DEF from ABCDEFGHI	Character values only: wrap a number in DIGITS first. Exceeding the field end gives an error.
DIGITS	Define result fields	DIGITS(numeric)	Character string of the digits, with leading zeros, no sign or decimal point	DIGITS(JOBCODE)	Length equals the number of digits in the field, so a 7,0 field gives 7 characters.
VALUE	Define result fields	VALUE(x, y)	First argument that is not null (same idea as SQL COALESCE)	VALUE(COMMISSION, 0)	Arguments must be compatible types: no mixing character and numeric.
VARCHAR	Define result fields	VARCHAR(expression)	Variable-length character result	VARCHAR(CUSNAM)	Useful before concatenation to avoid fixed padding.
CHAR	Define result fields	CHAR(date-or-time, format)	Character form of a date, time or timestamp	CHAR(ORDDATE, ISO)	Needed before LIKE or SUBSTR on a date field.
DATE	Define result fields	DATE(expression)	Converts a timestamp, a day number, a valid date string, or a yyyynnn string to a date	DATE('2026-10-22')	Two-digit year formats only cover 1940 to 2039; out-of-range values print as +.
DAYS	Define result fields	DAYS(date)	Day number since 0001-01-01; subtract two DAYS() for a day count	DAYS(CURRDATE) - DAYS(HIREDATE)	Subtracting two dates directly gives a yyyymmdd duration, not a day count.
YEAR / MONTH / DAY	Define result fields	YEAR(date)	Year, month or day part of a date, timestamp or duration	YEAR(ORDDATE)	Result is binary numeric; good as a break field.
TIME / TIMESTAMP and HOUR, MINUTE, SECOND, MICROSECOND	Define result fields	HOUR(time)	Time and timestamp conversions and parts	HOUR(ENTTIME)	Time comparisons always include seconds.
CURRENT	Define result fields	CURRENT(DATE) / CURRENT(TIME) / CURRENT(TIMESTAMP) / CURRENT(TIMEZONE)	System clock value; one reading per query run	TODAY = CURRENT(DATE)	Define it as a result field, then compare other date fields to it in Select records.
Labeled duration	Define result fields	date + n DAYS / MONTHS / YEARS	Date arithmetic	CURRENT(DATE) - 30 DAYS	HIREDATE + 2 MONTHS + 14 DAYS is valid; HIREDATE + (2 MONTHS + 14 DAYS) is not.
Report breaks	Run and output	Define report breaks: level 1-6	Group lines and print summaries at each change	Level 1 = CUSNO, level 2 = ORDNO	Up to 6 break levels; sort on the break fields in the same order or the groups split.
Summary functions	Run and output	1=Total 2=Average 3=Minimum 4=Maximum 5=Count	Totals per break level and for the report	Total on ORDAMT	Count counts non-null values.
Output type	Run and output	1=Display 2=Printer 3=Database file	Where the result goes	3 to write MYLIB/ORDEXT	Data in file: 1=New file, 2=Replace file, 3=New member, 4=Replace member, 5=Add to member. Replace file deletes all members first.
Form of output	Run and output	1=Detail 2=Summary only	Detail lines or only the break totals	2 for a customer totals file	Summary-only output to a database file holds one row per break.
RUNQRY	Run and output	RUNQRY QRY(lib/qry) RCDSLT(*YES)	Run a saved query; RCDSLT(*YES) shows Select Records before running	RUNQRY QRY(MYLIB/OPENORD) RCDSLT(*YES)	Changes made at run time are not saved to the definition.
RUNQRY to a file	Run and output	RUNQRY QRY(lib/qry) OUTTYPE(*OUTFILE) OUTFILE(lib/file)	Override output to a database file	RUNQRY QRY(MYLIB/OPENORD) OUTTYPE(*OUTFILE) OUTFILE(QTEMP/OPENORD)	Good for scheduling with ADDJOBSCDE.
RUNQRY default query	Run and output	RUNQRY QRYFILE(lib/file)	Quick look at a file with no saved query	RUNQRY QRYFILE(MYLIB/CUSTMAST)	Displays every record and field of the file; add RCDSLT(*YES) to filter first on large files.
CYYMMDD to date	Recipe	YMD8 = ORDDT + 19000000; C8 = DIGITS(YMD8); ISO = SUBSTR(C8,1,4) \|\| '-' \|\| SUBSTR(C8,5,2) \|\| '-' \|\| SUBSTR(C8,7,2); ODATE = DATE(ISO)	Turns a 7,0 CYYMMDD number (1261022) into a real date	1261022 + 19000000 = 20261022, then 2026-10-22	Give YMD8 Len 8 Dec 0. Zero or invalid dates in the field fail the DATE step: select ORDDT GT 0 first.
Last 30 days	Recipe	FROMDT = CURRENT(DATE) - 30 DAYS; select ODATE GE FROMDT	Rolling date window with no prompts	Uses the ODATE result field from the recipe above	Result fields can be used in Select Records once defined.
Numeric to text for LIKE	Recipe	ITEMC = DIGITS(ITEMNO); select ITEMC LIKE '45%'	Pattern test on a numeric field	Finds items starting 45	DIGITS keeps leading zeros, so 00045 does not start with 45.
Null-safe total	Recipe	AMT0 = VALUE(AMT, 0)	Avoids null results in arithmetic after an outer join	TOTAL = AMT0 * QTY	Any null argument makes the whole expression null.
Convert to SQL	Recipe	RTVQMQRY QMQRY(lib/qry) SRCFILE(lib/QQMQRYSRC) ALWQRYDFN(*YES)	Retrieves the SQL behind a Query/400 definition into a source member	RTVQMQRY QMQRY(MYLIB/OPENORD) SRCFILE(MYLIB/QQMQRYSRC) ALWQRYDFN(*YES)	Review the SQL: some report formatting and break options have no SQL equivalent.
Run a definition through Query Manager	Recipe	STRQMQRY QMQRY(lib/qry) ALWQRYDFN(*YES)	Runs the query definition with Query Manager	STRQMQRY QMQRY(MYLIB/OPENORD) OUTPUT(PRINT) ALWQRYDFN(YES)	Report layout differs from RUNQRY output.

Syntax and rules follow IBM's Query for IBM i Use guide (SC41-5210), chapters Selecting records and Defining result fields. Field names in examples are illustrative; use your own file's fields from the bottom of each display.

Get a WRKQRY report converted to SQL

Send one Query/400 definition printout and we will return the equivalent SQL view with the date conversions done properly.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search the table for the test or function you need, for example LIKE, LIST or SUBSTR, and copy the syntax exactly as shown into the Field, Test and Value columns. Filter on Recipe to see worked combinations such as turning an ERP CYYMMDD numeric date into a real date and selecting the last 30 days. Each row has the one gotcha that most often breaks it, for example LIKE patterns failing on trailing blanks or LIST values without apostrophes. Export to Excel if you want to keep a desk copy next to the green screen.

Where this lives in Query/400

Start with WRKQRY, type 2 next to a query to change it or 1 to create one, and you reach the Define the Query display with its list of options: Specify file selections, Define result fields, Select and sequence fields, Select records, Select sort fields, Define report breaks, Select output type and output form, and more. Type 1 next to the options you need, or press F21 to select all of them. Result fields are defined before Select records, so a result field built with SUBSTR or DATE can be used as a selection field. RUNQRY runs a saved query from the command line or a CL program, and RCDSLT(*YES) lets the operator change the selection at run time.

WRKQRY options: 1=Create 2=Change 3=Copy 4=Delete 5=Display 6=Print definition 8=Run in batch 9=Run
F13 shows the report layout while you work

Worked example: open orders for three states in 2026

Assume an order header file with STATE, ORDDT (7,0 CYYMMDD) and OPNAMT. In Define result fields add YMD8 = ORDDT + 19000000 with Len 8 Dec 0, C8 = DIGITS(YMD8), ISO = SUBSTR(C8,1,4) || '-' || SUBSTR(C8,5,2) || '-' || SUBSTR(C8,7,2) and ODATE = DATE(ISO). In Select records type STATE LIST 'TX' 'OK' 'LA', then AND YMD8 RANGE 20260101 20261231, then AND OPNAMT GT 0. Add report break level 1 on STATE with Total on OPNAMT and choose output type 3 to write the result to a work file another job can pick up.

Moving to SQL equivalents

Most Query/400 logic maps directly to SQL: EQ to =, LIST to IN, NLIST to NOT IN, RANGE to BETWEEN, LIKE and NLIKE to LIKE and NOT LIKE, IS and ISNOT to IS NULL and IS NOT NULL, VALUE to COALESCE, and labeled durations work unchanged in Db2 for i. RTVQMQRY with ALWQRYDFN(*YES) writes the SQL that IBM generates from the definition into a source member, which is the fastest starting point. Expect to rework report breaks and summary-only output into GROUP BY. Running the SQL through Run SQL Scripts or a view also lets other tools read the same result.

Frequently Asked Questions

How do I use LIKE in an AS400 query?

Type the field name, LIKE in the Test column, and a pattern in apostrophes in the Value column, for example CUSNAM LIKE '%SMITH%'. Percent matches zero or more characters and underscore matches exactly one. Matching is exact, so case matters, and trailing blanks count: '%A' fails on 'ANNA ' in an 8-character field, while '%A%' works.

What is the syntax for LIST in Query/400?

Use FIELD LIST followed by the values separated by blanks, with each character value in its own apostrophes, for example STATE LIST 'TX' 'OK' 'LA'. Numeric values have no apostrophes. Field names are not allowed in a list. Use NLIST for not in the list. If the values do not fit, continue on the next Value line with a leading blank.

Is RANGE inclusive in Query/400?

Yes. RANGE selects values greater than or equal to the first value and less than or equal to the second, typed with a blank between them, for example ORDDT RANGE 1260101 1261231. If the first value is higher than the second, nothing is selected, which is a common mistake when typing dates in descending order.

How do I use SUBSTR on a numeric field in AS400 query?

SUBSTR only works on character values. First make a result field with DIGITS(field), which returns the digits with leading zeros, then apply SUBSTR to that result field, for example SUBSTR(DIGITS(ORDDT),2,2) for the year part of a CYYMMDD date. Remember DIGITS keeps the full field length.

If your users spend their day building WRKQRY reports to answer ERP questions, we can put a private assistant on your IBM i data that answers in plain English and shows the SQL it ran.

Talk to an Expert Explore More Free Tools