SQL coding in RPGLE Free format

85 pts.
Tags:
AS/400
How can I improve the coding of this SQLRPGLE program? 0030.00 d MoreRows s 1a 0032.00 d recn s 7 0 0033.00 d pyam s 11 2 0036.00 d 0037.00 d Seq# s 9 0 0038.00 d sds 0039.00 d usid 254 263 0046.00 /Free 0048.00 Exec sql 0049.00 declare c1 cursor 0050.00 for select sum(utcbal), count(*) 0051.00 From ut251ap as a join ut210ap as b 0052.00 on a.utcsid = b.utcsid 0053.00 and a.utlcid = b.utlcid 0054.00 Where utclst = 'F' 0057.00 for read only; 0057.02 Exec sql 0057.03 open c1; 0057.05 If (SQLstt = '00000'); 0057.06 MoreRows = *on; 0057.07 EndIf; 0057.09 DoW (MoreRows = *on); 0057.11 Exec sql 0057.12 Fetch next 0057.13 From c1 0057.14 Into :pyam, 0057.15 :recn; 0057.18 If (SQLstt = '00000'); 0057.20 Seq# +=1; 0057.22 Exec sql 0057.23 Insert Into TESTSQLFIL 0057.24 ( CRRECN, 0057.26 CRPYAM ) 0057.27 Values ( :recn, 0057.29 :pyam ) 0057.30 With NC; 0057.33 If (SQLcod 0); 0057.34 MoreRows = *off; 0057.35 EndIf; 0057.36 Else; 0057.37 MoreRows = *off; 0057.38 EndIf; 0057.39 EndDo; 0057.41 exec sql 0057.42 Close c1; 0058.01 DSPLY pyam; 0058.02 DSPLY recn; 0100.00 0100.01 *InLR = *on; 0100.02 Return; 0114.00 /End-Free

Software/Hardware used:
AS/400

Answer Wiki

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

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
  • CharlieBrowne
    Can you repost the code using  [code] to insert 
    41,380 pointsBadges:
    report
  • TomLiotta
    For me, the biggest improvement would be to throw it away and replace the whole thing with this: Insert Into TESTSQLFIL select sum(utcbal), count(*) From ut251ap as a join ut210ap as b on a.utcsid = b.utcsid and a.utlcid = b.utlcid Where utclst = 'F'   A single SQL statement is all that's needed.   Now we'll see how many times this needs to be posted before it becomes readable.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Well, that wasn't too bad. It needs to be noted that that is a single statement. It is not a separate INSERT and SELECT. You should be able to copy/paste the whole sequence beginning with [ INSERT ] and ending with [ Where utclst = ‘F’ ] into a single STRSQL command line.   Tom
    125,585 pointsBadges:
    report
  • sorella
    Thanks Tom
    85 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