Whenever you use OR and AND operators in ACL (or other software, for that matter), be careful to ensure that you receive the results that you are looking for.
Assume you have Table1, which contains 100 loan transactions. 10 of those transactions have a loan rate of 5% and 10 transactions have a rate of 6%. The remaining transactions have rates above 10%.
You want to create a table of transactions (Table2) that does NOT contain any transactions with a rate of 5% or 6%, so you use this statement:
EXTRACT RECORD IF rate <> .05 OR rate <> .06 TO “Table2”
Will that work? And more importantly, do you always determine the result you’re looking for and then compare them to the results you receive?
No, that statement will NOT work. Table2 will contain all 100 transactions, including those with 5% and 6% rates.
Do you know why?
First, transactions with rates other than 5% or 6% will be extracted; that’s easy to understand.
Second, transactions with a rate of 5% will be extracted. Let’s look at each test to determine why.
- rate <> .05 = false but rate <> .06 is true. Since the operator is OR, if either of the expressions is true, the transaction will be extracted.
- rate <> .05 = true, but rate <> .06 is false. Again, if either of the expressions is true, the transaction will be extracted.
The correct statement is:
EXTRACT RECORD IF rate <> .05 AND rate <> .06 TO “Table2”
When AND is used, both tests must be true for the transaction to be extracted. Since one of the statements is always false when the rate = 5% or 6%, those transactions are not extracted, which is what you want.
How to Test Your Statements
1) Identify the number of transactions (records) in Table1.
2) Identify the number of 5% and 6% transactions that you want to exclude (use a filter on Table1).
3) After you THINK you’ve excluded the right transactions, compare the total records in Table1 and Table2. If they are the same, either no 5% or 6% transactions exist in Table1 (which you should already know from step 1) or your extract statement is faulty. If the record totals are the same, fix your statement. If the record totals are different, go to step 4.
4) Subtract the total records in Table2 from the total records from Table1. If the result equals the number of 5% and 6% transactions from step 2, your extract statement is correct.
ITauditSecurity 