RE: Shifting Data in Return SQL Statement

Ok first off...SQL normally doesn't like output only variables for host
variables.

     A            PARTNO    R        O 10  4
     A            PARTCL    R        O 10 23
     A            SERIALNO  R        O 10 35
     A            PQTY      R        O 10 54EDTCDE(3)
     A            PQDATEIN  R        O 10 61
     A            PQDATEOUT R        O 10 70
     A            PQSTATUSD     12A  O 11  4

Unless they are refined for input somewhere I'm surprised it passed the
precompiler check.  If they ARE in fact redefined somewhere in the
program can you show us that as well??

Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian Piotrowski
Sent: Monday, January 30, 2006 1:54 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Shifting Data in Return SQL Statement

Thanks to all who replied.

As Mark noted, it's probably a field definition issue, but I'm having a
heck of a time finding it.  I also found this problem in another
subroutine that calls the data.  Here's the select statement:

          SQLstmt = 'select partno, partcl, serialno, pqty';
          SQLstmt = SQLstmt + ' ,pqdatein, pqdateout, pqstatus FROM
PQR_PF';

          Select;
            when PQCAT = 'Q';
              SQLstmt = SQLstmt + 'WHERE PQSTATUS =' + apos + 'QUAR' +
apos;
            when PQCAT = 'R';
              SQLstmt = SQLstmt + 'WHERE PQSTATUS =' + apos + 'RLSD' +
apos;
            when PQCAT = 'S';
              SQLstmt = SQLstmt + 'WHERE PQSTATUS =' + apos + 'SCRP' +
apos;
            other;
              SQLstmt = SQLstmt + 'WHERE PQSTATUS <>' + apos + apos;
          endsl;

          Select;
            when %trim(PQDATEIN) <> '' and %trim(PQDATEOUT) <> '';
              SQLstmt = SQLstmt + ' AND pqdatein between ';
              SQLstmt = SQLstmt + apos + pqdatein + apos + ' AND ';
              SQLstmt = SQLstmt + apos + pqdateout+ apos;
            when %trim(PQDATEIN) <> '' and %trim(PQDATEOUT) = '';
              SQLstmt = SQLstmt + ' AND pqdatein >=';
              SQLstmt = SQLstmt + apos + pqdatein + apos;
            when %trim(PQDATEIN) = '' and %trim(PQDATEOUT) <> '';
              SQLstmt = SQLstmt + ' AND pqdateout >=';
              SQLstmt = SQLstmt + apos + pqdateout + apos;
            other;
              SQLStmt = SQLStmt;
          EndSL;
          SQLstmt = SQLstmt + ' and PQTY <> 0';
          SQLstmt = SQLstmt + ' and plcd = ' + apos + plcd + apos;
          SQLstmt = SQLstmt + ' ORDER BY pqdatein';

The FETCH INTO:

     C/exec SQL
     C+ FETCH pqcsr
     C+ INTO :partno, :partcl, :serialno, :pqty, :pqdatein,
     C+ :pqdateout, :pqstatus
     C/end-exec
     C*

The FETCH NEXT:
     C*
     C/exec SQL
     C+ FETCH next from pqcsr
     C+ INTO :partno, :partcl, :serialno, :pqty, :pqdatein,
     C+ :pqdateout, :pqstatus
     C/end-exec
     C*

The Subfile DDS to which the data is written (on screen):
     A** Subfile data record
     A          R PQDATA                    SFL CHGINPDFT(CS)
     A            OPTION         1A  I 10  2VALUES(' ' '1' 'S' 'R' 'L'
'H')
     A            PARTNO    R        O 10  4
     A            PARTCL    R        O 10 23
     A            SERIALNO  R        O 10 35
     A            PQTY      R        O 10 54EDTCDE(3)
     A            PQDATEIN  R        O 10 61
     A            PQDATEOUT R        O 10 70
     A            PQSTATUSD     12A  O 11  4

And finally, the PF DDS:

     A            PLCD           1A         TEXT('Plant Code')
     A            PARTNO        18A         TEXT('Part Number')
     A            PARTNAME      25A         TEXT('Part Name')
     A            PARTCL        11A         TEXT('Part Colour')
     A            SERIALNO      18A         TEXT('Serial Number')
     A            PQTY           5S 0       TEXT('Quantity')
     A            PQUSER        11A         TEXT('PQ USER')
     A            PQDATEIN       8A         TEXT('PQ Date In')
     A            PQTIMEIN       8A         TEXT('PQ Time In')
     A            PQDATEOUT      8A         TEXT('PQ Date Out')
     A            PQTIMEOUT      8A         TEXT('PQ Time Out')
     A            PQSTATUS       4A         TEXT('Disposition')
     A            OSERIALNO     18A         TEXT('ORIGINAL S/N')
     A            IDATE           Z         TEXT('INSERT DATE')

Thanks again,

Brian.

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Monday, January 30, 2006 12:52 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Shifting Data in Return SQL Statement

1 - Paste either the "fetch into" or the "select into" statement.
2 - Paste where the variables used in the "... into" are defined.

Rob Berendt