SQL -302 on OPEN cursor in SQLRPGLE

Question

The problem

I've got a SQLRPGLE program that executes queries that look like this:

SELECT orapdt, oraptm, orodr#, c.ccctls, orbill, b.cuslmn, b.cusvrp, orocty, orost, o.cubzip, o.cucnty, ordcty, ordst, d.cubzip, d.cucnty
FROM order
LEFT JOIN cmtctlf c ON orbill = c.cccode
LEFT JOIN custmast b ON orbill = b.cucode
LEFT JOIN custmast o ON orldat = o.cucode
LEFT JOIN custmast d ON orcons = d.cucode
WHERE
    orstat != 'C' AND
    orbill IN ('ABCDE', 'VWXYZ', 'JKFRTE') AND
    orapdt BETWEEN 2012365 AND 2013362 AND
    o.cucnty = 'USA' AND 
    (o.cubzip LIKE '760%' OR o.cubzip LIKE '761%' OR o.cubzip LIKE '762%') AND
    d.cubzip = '38652' AND
    ordcty = 'NA' AND
    ordst = 'MS' AND 
    d.cucnty = 'USA'
ORDER BY orapdt, oraptm, orodr#

Field definitions:

orapdt      7  0
oraptm      4a
orodr#      7a
c.ccctls    6a
orbill      6a
b.cuslmn    2a
b.cusvrp    3a
orocty      4a
orost       2a
o.cubzip    5a
o.cucnty    3a
ordcty      4a
ordst       2a
d.cubzip    5a
d.cucnty    3a
c.cccode    6a
b.cucode    6a
o.cucode    6a
d.cucode    6a

I see the following errors in my job log:

Field HVR0001 and value 1 not compatible. Reason 7.
Conversion error on host variable or parameter *N.

When I prompt for additional message information I'm told:

The attributes of variable field HVR0001 in query record format FORMAT0001 are not compatible with the attributes of value number 1. The value is *N. The reason code is 7.
7 -- Value contains numeric data that is not valid

and

Host variable or parameter *N or entry 1 in a descriptor area contains a value that cannot be converted to the attributes required by the statement. Error type 6 occurred.
6 -- Numeric data that is not valid.

These errors are triggered by opening the cursor:

...
exec sql PREPARE S1 FROM :sql_stmt;
exec sql DECLARE C1 SCROLL CURSOR FOR S1;
exec sql OPEN C1;
...

I also have QSQSVCDMP files in my outq filled with dump information. The only useful thing I see in there is a reference to CPF4278 and CPD4374

CPF4278 means Query definition template &1 not valid.

CPD4374 means Field &1 and value &3 not compatible. Reason &5.

Unfortunately the error message itself isn't there, only the strings "CPF4278" and "CPD4374".

In the program I monitor for SQL error codes and they are all the same:

SQLSTATE:  22023
SQLCODE:  -302
SQLERRMC:  <non-displayable character>*N

The error state/code means "A parameter or variable value is invalid."

What I've tried...

After much Googling I've tried:

1. removing the ORDER BY clause (on OPEN, data is fetched and ordered when there is an ORDER BY clause)
2. changing all LEFT JOIN's to INNER JOIN's (did this to make sure there were no NULL's in the result records from the right side)
3. adding " AND orapdt IS NOT NULL" to the WHERE clause
4. many more things that I've forgotten

What I'm asking...

How do I find out which field has bad data in it? I know that HVR0001 is invalid but which field is represented by HVR0001? I tried SELECTing fields in a different order but it's always HVR0001 that has an invalid value.

Ideally I'd like to be able to print out all HVR* fields/values so I can inspect them.

When I look at the compile listing there are no HVR* fields listed. There are some SQL_* fields listed and I can see that SQL_00011 is used to temporarily hold data that gets put into orapdt. SQL_00011 is defined exactly like orapdt (7,0 packed). That's the only numeric field in my query...

I feel like my problem is being caused by how the files are being joined, that somehow an invalid value (probably NULL) is being placed into my orapdt field.

I also think my problem has something to do with executing many of these queries one after the other (some of the WHERE specifics change for each query) because I can take one of the queries that fail and put it into it's own program and run it and it works fine.

This is on DB2 for i (V6R1) and all files involved were created using DDS

Edit: Here is the host variable (data structure) and the two external data structures needed for the LIKE statements:

d eds_custmast  e ds    extname('CUSTMAST') inz
d eds_order     e ds    extname('ORDER') inz

d o               ds
d orapdt                like(ORAPDT)
d oraptm                like(ORAPTM)
d orodr#                like(ORODR#)
d orctls                like(CUCODE)
d orbill                like(ORBILL)
d orslmn                like(CUSLMN)
d orcsr                 like(CUSVRP)
d orocty                like(OROCTY)
d orost                 like(OROST)
d orozip                like(CUBZIP)
d orocntry              like(CUCNTY)
d ordcty                like(ORDCTY)
d ordst                 like(ORDST)
d ordzip                like(CUBZIP)
d ordcntry              like(CUCNTY)

// Define an array to indicate nulls...
d o1nv            s     3i 0 dim(15)

And here's the fetch statement that actually gets the data:

dow sqlcode = *zeros;
   exec sql FETCH NEXT FROM C1 INTO :o :o1nv;

   if sqlcode = *zeros;
      // process the data.
   endif;
enddo;

exec sql CLOSE C1;

I didn't include this before simply because the error occurs when I'm OPENing the cursor, not FETCHing a row. The OPEN statement shouldn't know anything about the o data structure.

As for what changes in the WHERE clause - all of it is dynamically built (and thus can change) other than:

orstat != 'C' AND orapdt BETWEEN 2012365 AND 2013362

Answer 1

It's not at all easy to find out what the actual error is. I tend to copy statements like these into IBM i Navigator and use Visual Explain to try to get a grasp of what decisions the optimiser is making. Another way to do this is to do a STRDBG and look at the job log. When STRDBG is in effect, the optimiser puts informational messages into the job log. But even then, it cam be tough to puzzle out.

In this case, there's only one numeric column, orapdt. Try the query without that column and see if that's the culprit.