RPG400-L - SQL data offset during record retrieve

I'm wondering if anyone has run into this problem before. There's nothing
like it in the archives that I've been able to find.

I'm doing a retrieval using SQL in RPG ILE (V5R3) (first time developing in
SQL ILE as well) and I am getting a decimal data error on a packed field.
A file spec is listed below for an SQL generated table. After dumping the
data log to check the contents, I find that the contents of the field value
(QUANTITY) appears to indicate that the packed value is a null, based on
the dump log's results.

The actual data on this record is not null but in fact is populated with a
non-zero positive number. A check of the log dump indicates that this
positive number appears to have been moved into the UOM character field
that immediately follows the QUANTITY field in the record. I also noticed
that the UNITPRICE field is also considered as null, even though there is
valid data in the field.


CCINO BINARY 9 0 4 1 Both CCINO
Default value . . . . . . . . . . . . . . : None
LINENO BINARY 9 0 4 5 Both LINENO
Default value . . . . . . . . . . . . . . : None
PARTNO CHAR 40 42 9 Both PARTNO
Variable length field -- Allocated length : None
Allows the null value
Default value . . . . . . . . . . . . . . :
*NULL
Coded Character Set Identifier . . . . . : 37
PARTDESC CHAR 100 102 51 Both PARTDES
Variable length field -- Allocated length : None
Allows the null value
Default value . . . . . . . . . . . . . . : *NULL
Coded Character Set Identifier . . . . . : 37
ORIGIN CHAR 3 5 153 Both ORIGIN
Variable length field -- Allocated length : None
Allows the null value
Default value . . . . . . . . . . . . . . :
*NULL
Coded Character Set Identifier . . . . . : 37
HSCODE CHAR 13 15 158 Both HSCODE
Variable length field -- Allocated length : None
Allows the null value
Default value . . . . . . . . . . . . . . : *NULL
Coded Character Set Identifier . . . . . : 37
SERIALNO CHAR 20 22 173 Both SERIALNO
Variable length field -- Allocated length : None
Allows the null value
Default value . . . . . . . . . . . . . . : *NULL
Coded Character Set Identifier . . . . . : 37
JOBREF CHAR 20 22 195 Both JOBREF
Variable length field -- Allocated length : None
Allows the null value
Default value . . . . . . . . . . . . . . : *NULL
Coded Character Set Identifier . . . . . : 37
QUANTITY PACKED 13 5 7 217 Both QUANTITY
Allows the null value
Default value . . . . . . . . . . . . . . : *NULL
UOM CHAR 3 5 224 Both UOM
Variable length field -- Allocated length : None
Allows the null value
Default value . . . . . . . . . . . . . . : *NULL
Coded Character Set Identifier . . . . . : 37
UNITPRICE PACKED 13 5 7 229 Both UNITPRICE
Allows the null value
Default value . . . . . . . . . . . . . . : *NULL


Is there something unusual about packed fields being retrieved via SQL? I
have a null indicator array defined for retrieval to handle the null
fields. The SQL for the retrieval is provided below:
*
* Data structure for SQL result set for CCILINES read
D DetailSet E DS extname(CCILINES)
D prefix(D_)
*
D DetailInd S 5I 0 dim(11)
*
*
C/EXEC SQL
C+ Declare C02 CURSOR FOR
C+ Select B.CCINO, B.LINENO, B.PARTNO, B.PARTDESC,
C+ B.ORIGIN, B.HSCODE, B.HSCODE, B.SERIALNO,
C+ B.JOBREF, B.QUANTITY, B.UOM, B.UNITPRICE
C+ FROM CCILINES B
C+ WHERE B.CCINO = :CCINO_Nbr
C+ ORDER BY B.CCINO, B.LINENO
C/END-EXEC
*
* Open cursor to prep for reading of file
C/EXEC SQL
C+ Open C02
C/END-EXEC
*
* Read first record from cursor
C/EXEC SQL
C+ Fetch Next from C02 into :DetailSet:DetailInd
C/END-EXEC


Any help for a newbie is greatly appreciated.

Have a good day.


Blake Moorcroft
Developer - Corporate
Russell A. Farrow Limited
1980 Ambassador Drive, PO Box 333, Windsor, Ontario N9C 3R4
Bus: 519-966-3003 ext. 566, Fax: 519-966-9870
blake.moorcroft@xxxxxxxxxx