I received several messages and comments in response to my post Validation: CHAIN versus SETLL informing me that I can do the same in SQL.
Alas, no-one sent me any examples of how they would do it. Therefore, after some Googling I found a way.
In this example I will be using I will perform two "SETLLs" to a file, ORDFILE, looking for a match to the Order Number field, ORDNBR.
In the first scenario I used a valid Order Number and in the second an invalid number.
01 D OrdNo1 S 7 inz('MSFH540') 02 D OrdNo2 S like(OrdNo1) inz('???????') 03 D i S 5I 0 04 /free 05 exec sql select 1 into :i 06 from ordfile where ordnbr = :OrdNo1 ; 07 exec sql select 1 into :i 08 from ordfile where ordnbr = :OrdNo2 ;
Lines 1 and 2 define the work fields I will be using for the test order numbers.
On line 3 I have defined an integar field, i, that will be used to flag whether the "SETLL" was successful or not.
Lines 5 and 6 are the SQL statement that performs the look up matching the value in the field OrdNo1 to the field ORDNBR, which is successful and 1 is moved to i.
Lines 7 and 8 perform an unsuccessful match. As there is not a record in the ORDFILE with a key that matches, and no value is moved to i. If I check if i is equal to 1 to test if the match was successful then I would get a false positive, as i remains unchanged and equal to 1.
To overcome the possibility of false positives I would insert two new lines to move zero to i before performing the SQL statement, see below:
01 D OrdNo1 S 7 inz('MSFH540') 02 D OrdNo2 S like(OrdNo1) inz('???????') 03 D i S 5I 0 04 /free 05 i = 0 ; 06 exec sql select 1 into :i 07 from ordfile where ordnbr = :OrdNo1 ; 08 i = 0 ; 09 exec sql select 1 into :i 10 from ordfile where ordnbr = :OrdNo2 ;
Now when the unsuccessful "SETLL" is performed i is equal to zero.
Do you know a better way? If so add a Comment, below, describing how you would do it.
This article was written for IBM i 7.1, and it should work with earlier releases too.