DSPLY SQL value is zeros
I am trying to display the 2 result values from this SQL statement and they are zeros:
D totdlq S 11 2 inz(0)
D totcnt S 5 0 inz(0)
*
D totdel S 20a
D totrec S 10a
/Free
Exec sql DECLARE C1 CURSOR FOR
DynSqlStmt;
Exec sql open C1;
Exec sql
Select sum(utcbal), count(*)
INTO :totdlq, :totcnt
from ut251ap as a join ut210ap as
b on a.utcsid = b.utcsid and
a.utlcid = b.utlcid;
Exec sql close C1;
/End-Free
C MOVE totdlq totdel
C MOVE totcnt totrec
/Free
dou totrec = '';
dsply ' ' ' ' totdel;
dsply ' ' ' ' totrec;
totrec = '';
enddo;
Exsr EndPgm;
BegSr EndPgm;
*InLR = *on;
Return;
EndSr;
/End-Free
Software/Hardware used:
iSeries OS400 V5R4M0
Software/Hardware used:
iSeries OS400 V5R4M0
ASKED:
October 30, 2012 7:19 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.
There is so much wrong here …
You don’t really use any of this code …
Exec sql DECLARE C1 CURSOR FOR DynSqlStmt; Exec sql open C1; Exec sql close C1;
I would test the remaining code below in interactive SQL
Exec sql Select sum(utcbal), count(*) INTO :totdlq, :totcnt from ut251ap as a join ut210ap as b on a.utcsid = b.utcsid and a.utlcid = b.utlcid;
as
Select sum(utcbal), count(*) from ut251ap as a join ut210ap as b on a.utcsid = b.utcsid and a.utlcid = b.utlcid
and see if it’s creating data and if it’s within the field sizes
I mostly agree. There is nothing shown that explains why a CURSOR is declared It’s never used by the code that’s shown nor does the code indicate that a CURSOR is needed.
However, the SELECT statement should be okay even though it’s unrelated to the CURSOR (as far as we can see). Best guesses might be that the SELECT returned zeros for its values or that a SQL error code was returned but never tested. (And I’d get rid of the MOVE statements and use appropriate EVAL statements.)
Tom
I debugged the SQL statement and it created the correct values in the 2 fields, but I cannot have them displayed with the DSPLY code.
cut all of this
/End-Free C MOVE totdlq totdel C MOVE totcnt totrec /Free dou totrec = ”; dsply ‘ ‘ ‘ ‘ totdel; dsply ‘ ‘ ‘ ‘ totrec; totrec = ”; enddo; Exsr EndPgm; BegSr EndPgm; *InLR = *on; Return; EndSr; /End-Free
for this?dsply totdlq;
dsply totcnt;
*InLR = *on;
Return;
/End-Free
Thanks.
I found the problem:
UT251AP in *LIBL type *FILE not found.
Wrong library list.