Not sure, since I don't normally use null support, but isn't NL supposed
to be an array? Each field returns a corresponding null value, I think.
You then have to test the appropriate NL value for each field... I
think...
Wag on,
Eric
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Aaron Bartell
Sent: Wednesday, November 19, 2008 1:32 PM
To: RPG programming on the AS400 / iSeries
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
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
