SQL coding in RPGLE Free format
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
Software/Hardware used:
AS/400
ASKED:
November 6, 2012 3:57 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
Can you repost the code using to insert
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
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
Thanks
Tom