Re: Interesting SQL resultset issue

Hey Aaron,

I would try wrapping your OA.HTEORDNBR in a coelesce and setting it to some default value, probably 0 since rpg doesn't like Nulls too much.

coalesce(OA.HTEORDNBR,0) as OA.HTEORDNBR

see if that clears anything up. I'm not sure where else to start with this.... everything looks pretty good unless I'm missing something as well.


Thanks
Bryce Martin
Programmer/Analyst I
Ext. 4777



Aaron Bartell <aaronbartell@xxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
11/19/2008 02:32 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Interesting SQL resultset issue






Hi all,

I don't do a lot of embedded SQL compared to native access so forgive me
if this is a dumb mistake. I have a very simple SQL SELECT statement that
joins two files together (see code at bottom post). My primary table is
OMILOG and OMILOGOA is being joined to it.

When the SQL is run and a record is found in both tables, then everything
works fine and I get the expected results. When there isn't a JOIN match
in OIMLOGOA then everything still works *except* the O.CRTDT contains the
timestamp from the last previous successful JOIN in the result set. This
seems odd because all the other O. subfields are being populated just fine
with table information from OMILOG.

Any ideas?

(BTW, I trimmed down the program to just necessary parts, so if a
definition is missing you know why)

Aaron Bartell
http://mowyourlawn.com

H dftactgrp(*no) ALWNULL(*INPUTONLY)

/copy rxs,RXSCp

D NL s 3I 0

D gSQLRslt ds qualified inz
D hteOrdNbr 9a
D pid 15p 0
D uid 15p 0
D crtDt z
D pgmNam 30a
D svrty 10 0
D txt 256a
/free

*inlr = *on;

out('<table>' +
'<th>HTE Order<br/>Number</th>' +
'<th>Parent<br/>Id</th>' +
'<th>Unique<br/>Id</th>' +
'<th>Created</th>' +
'<th>Program<br/>Name</th>' +
'<th>Svrty</th>' +
'<th>Text</th>'
);
/end-free
C/EXEC SQL
C+ DECLARE omiLogCsr CURSOR FOR
C+ SELECT OA.HTEORDNBR,O.PID,O.UID,O.CRTDT,O.PGMNAM,O.SVRTY,O.TXT
C+ FROM omilog as O
C+ LEFT JOIN OMILOGOA as OA on OA.PID = O.PID
C+ ORDER BY o.pid DESC, o.uid DESC
C/END-EXEC
C/EXEC SQL
C+ OPEN omiLogCsr
C/END-EXEC

C DoU %Subst(SQLStt:1:2) = '02'

C/EXEC SQL
C+ FETCH NEXT FROM omiLogCsr INTO :gSQLRslt :NL
C/END-EXEC
/free

if %subst(SQLStt:1:2) = '00';

out('<tr>' +
'<td>' + gSQLRslt.hteOrdNbr + '</td>' +
'<td>' + %char(gSQLRslt.pid) + '</td>' +
'<td>' + %char(gSQLRslt.uid) + '</td>' +
'<td>' + %char(gSQLRslt.crtdt) + '</td>' +
'<td>' + gSQLRslt.pgmnam + '</td>' +
'<td>' + %char(gSQLRslt.svrty) + '</td>' +
'<td>' + %trim(gSQLRslt.txt) + '</td>' +
'</tr>'
);
else;
leave;
endif;

enddo;

out('</table>');

/end-free
C/EXEC SQL
C+ CLOSE omiLogCsr
C/END-EXEC
/free