RE: Shifting Data in Return SQL Statement

Brian,

You may want to check your field definitions to make sure they are
correct (proper lengths and types, not overlapping).

Matt

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

Hi All,

 

I have a subroutine that retrieves a record from a table based on passed
criteria.  It works fine in the sense that it returns the proper record.
However, the problem I am experiencing is that it is shifting the data
in the returned values.  For example:

 

If I enter "123456" as a serial number, there is a record in the
database that has these values:

Part Number: 1920120

Colour: RED

Serial Number: 123456

 

Before the record is retrieved, I checked the values of PARTNO, PARTCL
and SERIALNO (which were all blanks).  However, after I run the SQL
statement and retrieve the records, I get this data returned:

 

Part Number: 1920120 R

Colour: RED 1234

Serial Number: 123456

 

As you can see, the next field in the above sequence is putting data
into the previous field (ie: "R" in the Part Number Field).

 

Here is the code I am using for my subroutine:

        BegSR GetRecord;

      /end-free

     C*

     c/exec SQL

     c+ select partno, partname, partcl, serialno, pqty, pquser

     c+ , pqdatein, pqtimein, pqdateout, pqtimeout, pqstatus

     c+ INTO :partno, :partname, :partcl, :serialno, :pqty, :pquser,
:pqdatein,

     c+ :pqtimein, :pqdateout, :pqtimeout, :pqstatus

     c+ FROM PQR_PF

     c+ WHERE serialno= :serialno

     c/end-exec

     C*

      /free

          If pqstatus = 'RLSD';

            PQStatusP ='Released';

          endif;

 

          if pqstatus = 'SCRP';

            PQStatusP = 'Scrapped';

          endif;

 

          if pqstatus = 'QUAR';

            PQStatusP ='In Quarantine';

          endif;

 

          PQDateInD = %subst(pqdatein:1:4) + '/' + %subst(pqdatein:5:2)
+ '/'

                      + %subst(pqdatein:7:2) + ' ' + PQTimeIn;

 

          PQDateOutD = %subst(pqdateout:1:4) + '/' +
%subst(pqdateout:5:2) + '/'

                       + %subst(pqdateout:7:2) + ' ' + PQTimeout;

         EndSR;

 

Can anyone see any obvious reason why I am getting data spilling over
into my fields?

 

Thanks!


Brian.

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

PH: 705-435-7814

FX: 705-435-6746

-=-=-=-=-=-=-=-=-=-=-=-=-=-