Example 8: Selecting records using the Open Query File (OPNQRYF) command

 

This example shows how to select records using the wildcard function of the Open Query File (OPNQRYF) command. Assume that you have a packed decimal Date field in the format MMDDYY and you want to select the records for March 1988. To do this, you can specify:

OVRDBF     FILE(FILEA) SHARE(*YES)
OPNQRYF    FILE(FILEA) +
             QRYSLT('%DIGITS(DATE) *EQ %WLDCRD("03__88")')
CALL       PGM(PGMC)
CLOF       OPNID(FILEA)
DLTOVR     FILE(FILEA)

Note that the only time the MAPFLD parameter is needed to define a database field for the result of the %DIGITS function is when the result needs to be used with a function that only supports a simple field name (not a function or expression) as an argument. The %WLDCRD operation has no such restriction on the operand that appears before the *EQ operator.

Note that although the field in the database is in numeric form, quotation marks surround the literal to make its definition the same as the Char6 field. The wildcard function is not supported for DATE, TIME, or TIMESTAMP data types.

The %WLDCRD function lets you select any records that match your selection values, in which the underline (_) will match any single character value. The two underline characters in Example 8 allow any day in the month of March to be selected. The %WLDCRD function also allows you to name the wild card character (underline is the default). The wild card function supports two different forms:

• A fixed-position wild card as shown in the previous example in which the underline (or your designated character) matches any single character as in this example:

  QRYSLT('FLDA *EQ %WLDCRD("A_C")')

  This compares successfully to ABC, ACC, ADC, AxC, and so on. In this example, the field being analyzed only compares correctly if it is exactly 3 characters in length. If the field is longer than 3 characters, you also need the second form of wild card support.

• A variable-position wild card matches any zero or more characters. The Open Query File (OPNQRYF) command uses an asterisk (*) for this type of wild card variable character or you can specify your own character. An asterisk is used in this example:

  QRYSLT('FLDB *EQ %WLDCRD("A*C*") ')

  This compares successfully to AC, ABC, AxC, ABCD, AxxxxxxxC, and so on. The asterisk causes the command to ignore any intervening characters if they exist. Notice that in this example the asterisk is specified both before and after the character or characters that can appear later in the field. If the asterisk is omitted from the end of the search argument, it causes a selection only if the field ends with the character C.

  You must specify an asterisk at the start of the wild card string if you want to select records where the remainder of the pattern starts anywhere in the field. Similarly, the pattern string must end with an asterisk if you want to select records where the remainder of the pattern ends anywhere in the field. For example, you can specify:

  QRYSLT('FLDB *EQ %WLDCRD("*ABC*DEF*") ')

  You get a match on ABCDEF, ABCxDEF, ABCxDEFx, ABCxxxxxxDEF, ABCxxxDEFxxx, xABCDEF, xABCxDEFx, and so on.

You can combine the two wildcard functions as shown in this example:

QRYSLT('FLDB *EQ %WLDCRD("ABC_*DEF*") ')

You get a match on ABCxDEF, ABCxxxxxxDEF, ABCxxxDEFxxx, and so on. The underline forces at least one character to appear between the ABC and DEF (for example, ABCDEF would not match). Assume that you have a Name field that contains:

• JOHNS

• JOHNS SMITH

• JOHNSON

• JOHNSTON

You only gets the first record if you specify:

QRYSLT('NAME *EQ "JOHNS"')

You would not select the other records because a comparison is made with blanks added to the value you specified. The way to select all four names is to specify:

QRYSLT('NAME *EQ %WLDCRD("JOHNS*")')

 

Parent topic:

Selecting records using the Open Query File (OPNQRYF) command

 

Related concepts

Double-byte character set considerations
Control language