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

 

This example shows how to specify complex selection statements when you select records using the Open Query File (OPNQRYF) command. Complex selection statements can also be specified. For example, you can specify:

QRYSLT('DATE *EQ "880101" *AND AMT *GT 5000.00')
 
QRYSLT('DATE *EQ "880101" *OR AMT *GT 5000.00')

You can also specify:

QRYSLT('CODE *EQ "A" *AND TYPE *EQ "X" *OR CODE *EQ "B")

The rules governing the priority of processing the operators are described in the Control language (CL) topic. Some of the rules are:

• The *AND operations are processed first; therefore, the record would be selected if:

    The Code field = "A"   and    The Type field = "X"
        or
    The Code field = "B"

• Parentheses can be used to control how the expression is handled, as shown in this example:

  QRYSLT('(CODE *EQ "A" *OR CODE *EQ "B") *AND TYPE *EQ "X" +
           *OR CODE *EQ "C"')

    The Code field = "A"    and    The Type field= "X"
        or
    The Code field = "B"    and    The Type field = "X"
        or
    The Code field = "C"

You can also use the symbols described in the Control language (CL) topic, instead of the abbreviated form (for example, you can use = instead of *EQ), as shown in this example:

QRYSLT('CODE = "A" & TYPE = "X" | AMT > 5000.00')

This command selects all records in which:

  The Code field = "A"   and   The Type field = "X"
      or
  The Amt field > 5000.00 A complex selection statement can also be written like this:

QRYSLT('CUSNBR = %RANGE("60000" "69999") & TYPE = "B" +
       & SALES>0 & ACCRCV / SALES>.3')

This command selects all records in which:

    The Cusnbr field is in the range 60000-69999 and
    The Type field = "B" and
    The Sales fields are greater than 0 and
    Accrcv divided by Sales is greater than 30 percent

 

Parent topic:

Selecting records using the Open Query File (OPNQRYF) command