Re: Interesting SQL resultset issue

["Charles Wilt" <charles.wilt@xxxxxxxxx>]

Aaron,

My first thought...

As with nullable files fields, you must always check the null indicator
returned _BEFORE_ you access the data.

Actually I'm surprised this even compiles.  You usually need a indicator
structure, ie. an array of indicators, when you're returning into a DS.

Second thought....
Reading closer, the only nullable field is OA.HTEORDNBR.  But you are saying
that you are having a problem with O.CRTDT.

That doesn't make sense.  I don't think you realize exactly what you are
getting.

When you fetch a row that didn't have a match in OIMLOGOA, the value in
OA.HTEORDNBR is not changed, but the NULL indicator for that field is turned
on.  Perhaps the inccorect HTEORDNBR value is misleading you into thinking
O.CRTDT is wrong.

Charles

On Wed, Nov 19, 2008 at 2:32 PM, Aaron Bartell <aaronbartell@xxxxxxxxx>wrote:

>   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.