Odd sql states (22002 and 22003)

[jj_dahlheimer]

I have two sql states popping up that I cannot explain why they are coming up. They only come up in a specific scenario that I am unable to duplicate and I am not sure what I would learn if I did. Below is the code.

22002 requiring indicator variables, none of the fields are null capable and no left joins. What else could cause a null to come through?

22003 numeric value out of range, all fields are defined exactly as they are in the dds and no math is being done.

My only thought is that this process is being run during a process that has commit control, could trying to get uncommitted data cause the issue.

Dcl-Ds orderInfo qualified;
         priority packed(1);
         shipVia packed(2);
         lineOfBusiness char(3);
         enterDate packed(7);
         enterTime packed(6);
       End-Ds;

Exec Sql
             Select Dopsqt,
                    Dooxnb,
                    Dow5cg,
                    Dmd0dt,
                    Dmaztm
               Into :Orderinfo
               From Oeorst
               Join Oeordr On Dmcomp = Docomp And
                                      Dmjwnb = Dojwnb
               Where Docomp = :Company And
                     Dojwnb = :Order# And
                     Dojxnb = :Shipto#
               Fetch First Row Only;

[Vectorspace]

Can you find out the SQLCOD and SQLERM values when the error occurs? These together with SQLSTT 22002/22003 would give a more precise definition of the error, probably with the specific field that is erroring:

SQLCOD and SQLSTT together map to an iSeries Error Message ID, and SQLERM provides the substitution values for the message
You can look up the values in the IBM documentation on iSeries SQL messages and codes: https://www.ibm.com/support/knowledg...alakickoff.htm

[jj_dahlheimer]

I have dumps for both errors, the sqlcod for 22003 is -304 and for 22002 it is -305. The SQLERM for both is "PRIORITY". So that tells me is the value going into variable priority that is causing the issues?

I think that value is stored in the dump too. SQL_00008 PACKED(1,0) 0. '0F'X

[B.Hauser]

The message textes for any SQLCODE can be found in the QSQLMSG Message file.
The appropriate message id can be built as follows: SQ or SQL + the absolute value of the SQLCODE, i.e. -304 = SQL0304.
If you have a look at the message id for SQLCODE -304, it is: Conversion Error in assignment to variable &2. (and &2 seems to be your global variable PRIORITY).
How is the original column Dopsqt defined? Try to define it as Packed(3) or Int(3)

Birgitta

[jj_dahlheimer]

dopsqt is defined as packed(1,0), are you suggesting I change that to 3,0 or change my program variable PRIORITY to 3,0?

[TedHolt]

Birgitta is suggesting that you change the definition of the priority field in the data structure, not that you change the definition of dopsqt in the database.

Just for grins, I would add an array of indicator variables. That should get rid of 22002. I wonder if 22003 would also go away.

[jj_dahlheimer]

I was thinking about adding the indicators but it just did not make a lot of sense to me as to why it would do it. Ill probably try a few things on Monday.

[Vectorspace]

You say you cannot reproduce this error. I have a thought on that. Try writing a test program that reads the same SQL but with no Where statement, and no "fetch first", in a cursor loop so it loops through every record on both files. Since it's ever record rather than a specific company/order/shipto, you have a higher chance of hitting the error. You could also add the RRNs or unique key fields for each file, so you can identify the record from the dump.

Can you show us the definitions for each file? Are they DDS or SQL?