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