Selecting Records for Your Report - IF vs. WHERE

Document ID : KB000056220
Last Modified Date : 14/02/2018
Show Technical Document Details

CA-Ramis provides two types of selection for reporting. The IF selection statement allows a variety of selection conditions to be specified, but only one condition may be specified per IF statement. The test values within a selection condition may be connected by an 'OR', but multiple selection conditions (i.e., multiple IF statements) are always treated as if they were connected by an 'AND' conjunction. In contrast, the WHERE selection statement may contain multiple selection conditions in a single statement, connected by a combination of 'AND' and 'OR' conjunctions. This allows for complex selection conditions to be specified with a single WHERE selection statement.

Suppose you already have a request which uses IF selection. Should you convert from IF selection to WHERE selection? And, if you decide to convert to WHERE selection, what should you know?

Because WHERE selection is more complex than IF selection, the associated processing is also more complex, and will require more system resources. So unless you need the complexity provided by WHERE selection, you should continue using IF selection. For example, the following request will select records for customer 66902 only, and only when the sale was for ELECTRONICS and made in November of 1995 or December of 1995:

TABLE
FILE SALESDATA
PRINT CUSTNUM AND PTYPE AND MONTH AND UNITS AND VALUE
IF CUSTNUM IS 66902
IF PTYPE IS ELECTRONICS
IF MONTH IS 9511 OR 9512
END

The result of this request is shown below:

RP0808:  Number of records in table=          3, lines=          3  
PAGE     1  
CUSTNUM  PRODTYPE     MONTH  UNITS  VALUE  
-------  --------     -----  -----  -----  
66902    ELECTRONICS  9511       7  $10,500.00 
66902    ELECTRONICS  9511       1  $13,650.00 
66902    ELECTRONICS  9512       1  $38,850.00

The same results can be achieved by changing each IF to a WHERE, repeating the test field and operation for each 'OR' connected test value, and enclosing the alphanumeric literal ELECTRONICS in single quotes:

TABLE
FILE SALESDATA
PRINT CUSTNUM AND PTYPE AND MONTH AND UNITS AND VALUE
WHERE CUSTNUM IS 66902
WHERE PTYPE IS 'ELECTRONICS'
WHERE MONTH IS 9511 OR MONTH IS 9512
END

Just as multiple IF statements are treated as though they were 'AND' connected, so, too are multiple WHERE statements.

Now, suppose you want to write the same request with a single WHERE statement. If you simply change the subsequent WHERE commands to 'AND' conjunctions, you will get the following results:

TABLE
FILE SALESDATA
PRINT CUSTNUM AND PTYPE AND MONTH AND UNITS AND VALUE
WHERE CUSTNUM IS 66902
AND PTYPE IS 'ELECTRONICS'
AND MONTH IS 9511 OR MONTH IS 9512
END

RP0808:  Number of records in table=         49, lines=         49  
PAGE     1  
CUSTNUM  PRODTYPE     MONTH  UNITS  VALUE  
-------  --------     -----  -----  -----  
11043    FILES        9512      20    $19,000.00 
11043    ELECTRONICS  9512       2    $27,000.00 
12700    FURNITURE    9512      16     $5,000.00 
12700    ELECTRONICS  9512       5       $350.45  
12700    FURNITURE    9512      10       $978.50  
12700    FILES        9512       2     $1,935.84  
12700    FURNITURE    9512       3     $1,432.74  
12700    TERMINALS    9512       1     $2,143.80  
12700    TERMINALS    9512       2     $2,425.00 
12700    ELECTRONICS  9512      15    $20,000.00 
12700    ELECTRONICS  9512       2    $27,000.00 
23015    ELECTRONICS  9512       1    $38,600.00 
25910    FURNITURE    9512      30    $10,000.00 
25910    FURNITURE    9512      30    $15,600.00 
25910    TERMINALS    9512      10    $20,000.00 
25910    ELECTRONICS  9512       1     $1,500.00 
52814    FURNITURE    9512      10     $3,250.00 
52814    ELECTRONICS  9512      20     $1,300.00 
52814    TERMINALS    9512      10    $12,000.00 
52814    ELECTRONICS  9512       1    $13,650.00 
57119    ELECTRONICS  9512       1    $13,600.00 
62046    FURNITURE    9512       7       $684.95  
62046    FURNITURE    9512       6     $2,865.48  
62046    TERMINALS    9512       8    $17,150.40  
62046    TERMINALS    9512       7     $8,487.50  
66902    TERMINALS    9512       8     $9,700.00 
66902    ELECTRONICS  9511       7    $10,500.00 
66902    ELECTRONICS  9511       1    $13,650.00 
66902    ELECTRONICS  9512       1    $38,850.00 
69245    FURNITURE    9512      14     $1,850.00 
69245    FURNITURE    9512      12     $4,800.00 
69245    FILES        9512      24     $4,300.00 
69245    TERMINALS    9512      12    $24,700.00 
89201    FURNITURE    9512      12     $3,800.00 
89201    FURNITURE    9512      24     $2,348.40  
89201    FURNITURE    9512      50    $18,000.00 
89201    FURNITURE    9512       7     $3,343.06  
89201    TERMINALS    9512       8    $17,150.40  
89201    TERMINALS    9512       6     $7,275.00 
99476    FURNITURE    9512      10     $1,400.00 
99476    FURNITURE    9512      48     $4,250.00 
99476    FURNITURE    9512      11     $4,014.89  
99476    FURNITURE    9512      24     $9,750.00 
99476    FURNITURE    9512       9     $4,684.32  
99476    FILES        9512       8     $7,743.36  
99476    FURNITURE    9512       3     $1,432.74  
99476    TERMINALS    9512       8     $9,700.00 
99476    ELECTRONICS  9512       1    $13,650.00 
99476    ELECTRONICS  9512       1    $38,850.00

Not quite the results we expected! Note the lack of parentheses in the previous example. When constructing WHERE clauses, parentheses should be used to avoid ambiguity, even when the request itself does not seem to require them. In fact, omitting parentheses may result in an ambiguous selection statement and unexpected results, as you can see from this example.

To write the above WHERE selection in a single statement, we have to add parentheses to the request, as shown below:

TABLE
FILE SALESDATA
PRINT CUSTNUM AND PTYPE AND MONTH AND UNITS AND VALUE
WHERE CUSTNUM IS 66902
AND PTYPE IS 'ELECTRONICS'
AND ( MONTH IS 9511 OR MONTH IS 9512 )
END

This request will now give the expected results (identical to the first 2 examples):

RP0808:  Number of records in table=          3, lines=          3  
PAGE     1  
CUSTNUM  PRODTYPE     MONTH  UNITS  VALUE  
-------  --------     -----  -----  -----  
66902    ELECTRONICS  9511       7    $10,500.00 
66902    ELECTRONICS  9511       1    $13,650.00 
66902    ELECTRONICS  9512       1    $38,850.00

None of the examples provided thus far require the complex Boolean support provided by WHERE selection. In all of the above examples, IF selection would achieve the same results, with slightly better overall performance. However, suppose you wanted to see all the records for customer 66902 for the months of November of 1995 and December of 1995, plus all other customers who purchased ELECTRONICS during November of 1995 or December of 1995. To obtain the desired records, it is necessary to use WHERE selection, since there are two selection conditions (customer 66902 and ELECTRONICS) which must be 'OR' connected. Your request would look like this:

TABLE
FILE SALESDATA
PRINT CUSTNUM AND PTYPE AND MONTH AND UNITS AND VALUE
WHERE ( CUSTNUM IS 66902 OR PTYPE IS 'ELECTRONICS' )
AND ( MONTH IS 9511 OR MONTH IS 9512 )
END

The report produced by this request follows:

RP0808:  Number of records in table=         27, lines=         27  
PAGE     1  
CUSTNUM  PRODTYPE     MONTH  UNITS  VALUE  
-------  --------     -----  -----  -----  
11043    ELECTRONICS  9511       1     $1,500.00 
11043    ELECTRONICS  9512       2    $27,000.00 
12700    ELECTRONICS  9512       5       $350.45  
12700    ELECTRONICS  9512      15    $20,000.00 
12700    ELECTRONICS  9512       2    $27,000.00 
12700    ELECTRONICS  9511       1    $38,850.00 
23015    ELECTRONICS  9511       4     $5,000.00 
23015    ELECTRONICS  9512       1    $38,600.00 
25910    ELECTRONICS  9512       1     $1,500.00 
52814    ELECTRONICS  9512      20     $1,300.00 
52814    ELECTRONICS  9511       2     $3,000.00 
52814    ELECTRONICS  9511       1    $13,650.00 
52814    ELECTRONICS  9512       1    $13,650.00 
57119    ELECTRONICS  9512       1    $13,600.00 
62046    ELECTRONICS  9511       2    $27,300.00 
66902    FURNITURE    9511      36    $12,480.00 
66902    FURNITURE    9511       4     $1,660.00 
66902    FURNITURE    9511      14     $7,200.00 
66902    TERMINALS    9512       8     $9,700.00 
66902    ELECTRONICS  9511       7    $10,500.00 
66902    ELECTRONICS  9511       1    $13,650.00 
66902    ELECTRONICS  9512       1    $38,850.00 
69245    ELECTRONICS  9511       1    $38,850.00 
89201    ELECTRONICS  9511       3    $40,000.00 
99476    ELECTRONICS  9511      15     $1,051.35 
99476    ELECTRONICS  9512       1    $13,650.00 
99476    ELECTRONICS  9512       1    $38,850.00 

In this example, parentheses are necessary to correctly define the selection criteria. Omission of either pair would give different results. Try it for yourself!

Read More About It

For more information on the selection process in reporting, please refer to the CA-Ramis publication, Syntax Based Reporting.

Contribute Your Ideas

If you have a tried and true technique that you would like to share with the CA-Ramis community as a published article, please contact Computer Associates Support.