Testing RPGSQL for the number of records.

940 pts.
Tags:
RPGSQL
SQL
SQL statement
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
UPDATED: September 20, 2010  1:39 PM

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    Tom - Slap-me-up-side-of-head! SMACK! > SELECT COUNT(*) Why didn't I think of that. Thanks Phil
    940 pointsBadges:
    report
  • TomLiotta
    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
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following