× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.