Selecting Records for Your Report - IF vs. WHERE
Article ID: 56220
Updated On:
Products
Issue/Introduction
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?
Resolution
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.
Additional Information
For more information on the selection process in reporting, please refer to the Ramis publication, Syntax Based Reporting.
The Ramis bookshelves are found here
Feedback
