Testing RPGSQL for the number of records.

MurrayInfoSys

940 pts.

Tags:

RPGSQL

SQL

SQL statements

V5R4M0

I need to update file A if I can match a record in file B using an SQL statement. (IE: is Work Order # 5 in file B.)

Im looking for something similar to a SETLL. When I OPEN the cursor, will the SQLCA give me some specific answer? Or do I have to actually FETCH the cursor?

Thanks,

Phil

Software/Hardware used:
OS V5R4M0

Asked: September 16, 2010 5:03 PM Last updated: September 20, 2010 1:39 PM

Answer Wiki

Last updated: September 20, 2010 1:38 PM GMT

slateken230 pts.
History
Contributors
- Ordered by most recent
- slateken230 pts.

Thanks. We'll let you know when a new response is added.

I’ve used Tom’s approach and it works well. The following method also works for checking that a record exists without performing the “count”.

[CODE
]D RecFound s 1a inz(‘N’)

/free

reset RecFound;

Exec SQL set :RecFound = (select ‘Y’ from sysibm/sysdummy1
where exists (select zip1 from MyZips where zip1 = 15205));</pre>

Discuss This Question: 4 Replies

TomLiotta Sep 16, 2010 10:31 PM GMT

I need to update file A... But not with SQL? That is, a SQL UPDATE statement could restrict itself only to rows in A that had a match in B. But it seems that that isn't exactly what you're after. You want to "select" the number of matching rows in B, and, if not zero, then you want to move on to some later code that will eventually result in an update to A after some preparation is done over the values. If that's reasonably accurate, then a potential solution could be SELECT COUNT(*) FROM FILEB WHERE {fileb=filea}. In there, COUNT(*) would be selected into a host variable that could be tested for zero. Of course, that's different from SETLL since multiple possible I/Os could take place and you are trying to avoid that... assuming I'm understanding what you want. Is the matching column indexed? Tom

125,585 pointsBadges:

report
MurrayInfoSys Sep 17, 2010 10:52 PM GMT

Tom - Slap-me-up-side-of-head! SMACK! > SELECT COUNT(*) Why didn't I think of that. Thanks Phil

940 pointsBadges:

report
TomLiotta Sep 18, 2010 1:36 AM GMT
I was wondering about indexing because I was hoping DB2 would be smart enough to check index-entry counts for an indexed column. IMO, that ought to make any physical I/O be a minimum. Just the page(s) with appropriate index tree elements should ever be hit. Maybe a SET :var statement:
```
exec sql set :RcdCnt = ( select count(*) from MYFILE ) ; 
```
Tom
125,585 pointsBadges:
report
slateken Sep 20, 2010 1:39 PM GMT
I've used Tom's approach and it works well. The following method also works for checking that a record exists without performing the "count".
```
D RecFound s 1a inz('N') 

/free 

reset RecFound; 

Exec SQL set :RecFound = (select 'Y' from sysibm/sysdummy1 
where exists (select zip1 from MyZips where zip1 = 15205));
```
230 pointsBadges:
report