SQLCODE 30 on fetch SQLRPGLE
Hello All,
Software/Hardware used:
iseries, v5r4
I'm trying to execute a fetch into DS on a SQL statement and keep getting code 30: Number of host variables less than result values.
The DS and SELECT statement both have the same number of values as far as I can see.
Here's the DS and select fields code:
D ClaimRecDS ds
D wlco
D wmco
D wclyr
D wclcd
D wclds
D wcno
D winnm
D wbroker 7A inz(*blanks)
D wagent 7A inz(*blanks)
D wsym
D wpol
D wmod
D wcols
D wrsst
D wocdt
D wrpdt
D wclsd
D wstat
D wstdesc
/FREE
exec sql declare ClaimRec cursor for
select zclco, zcmco, zcclyr, zcclcd, zcdesc,
coalesce(dbcno, 'NA') as insured, coalesce(dbinnm, 'NA') as name,
coalesce(dbbrk, 'NA') as broker, coalesce(dbagt, 'NA') as agent,
coalesce(dbpsym, 'NA') as symbol, coalesce(dbpno, 'NA') as policy,
coalesce(dbpmod, 'NA') as pmod, coalesce(dbcols, 'NA') as cols,
coalesce(dbrsst, 'NA') as rst, coalesce(dbocdt, 0) as ocdt,
coalesce(dbrpdt, 0) as rpdt, coalesce(dbclsd, 0) as clsd,
coalesce(dbstat, 'NA') as status, coalesce(xcdesc, 'NA') as statdesc
I count 19 for both.
Most of the fields described in the DS are defined in an external PF. Could that be why?
I thought it was because of NULL indicators, so I coalesced everything that can be null.
Thoughts?
Thanks,
Dave
Software/Hardware used:
iseries, v5r4
ASKED:
December 28, 2011 9:09 PM
UPDATED:
March 17, 2012 6:01 AM
Answer Wiki:
So here's what I found:
I took a deeper look in the dump and noticed the first two values from the select (zclco and zcmco) were being placed in the fields wbroker and wagent in the ClaimRecDS.
As you can see, those fields are clearly in the middle of the data structure.
I decided to go ahead and define the fields again in the data structure (even though they do exist in an external file). Low and behold everything fell into place.
Here's the corrected DS:
<pre>
D ClaimRecDS ds
D wlco 2A inz(*blanks)
D wmco 2A inz(*blanks)
D wclyr 4S 0 inz(0)
D wclcd 2S 0 inz(0)
D wclds 35A inz(*blanks)
D wcno 10A inz(*blanks)
D winnm 35A inz(*blanks)
D wbroker 7A inz(*blanks)
D wagent 7A inz(*blanks)
D wsym 3A inz(*blanks)
D wpol 7A inz(*blanks)
D wmod 2A inz(*blanks)
D wcols 4A inz(*blanks)
D wrsst 2A inz(*blanks)
D wocdt 8S 0 inz(0)
D wrpdt 8S 0 inz(0)
D wclsd 8S 0 inz(0)
D wstat 3A inz(*blanks)
D wstdesc 25A inz(*blanks)
</pre>
So I guess we can conclude you always have to define the sizes/types of the fields in a DS even if they're already defined elsewhere.
They always say taking a step away or sleeping on something definitely helps you notice things faster. I was looking for a few hours yesterday. Had it figured out in 15 minutes when I got here. Ridiculous.
Thanks for the help gents.
And yes Phil, I changed column "STATUS" in my query just for you. lol
To see all answers submitted to the Answer Wiki: View Answer History.
I gave up on trying to format this question nicely… Every time I changed it, it got worse. I apologize.
I see 19 – 19
And obviously you have an
exec sq OPEN ClaimRec;
and
exec sq Fetch next from ClaimRec into :ClaimRecDS;
While I’ve got nothing, I would try changing the column name STATUS just cause it feels special. Then I would try shortning both lists a few columns at a time till it feels good.
If there were too many host variables I would look below what we think is the end of the data structure to see if the next D spec was infact not a DS or S but that doesn’t appear to be the problem.
Phil
The count looks matching.
As you are using Data structure in Result field, You will need to verify the field length of Data Structure(These should match with the length of PF Fields).
Have you verified this one as well?
Pradeep.
Oh look, now all of a sudden my formatting is working too!
Technology… :-/
So I guess we can conclude you always have to define the sizes/types of the fields in a DS even if they’re already defined elsewhere.
I would suspect that something else is involved. In particular, I would be very suspicious of this:
I was looking for a few hours yesterday. Had it figured out in 15 minutes when I got here.
When something works easily at the start of a new day, be suspicious of the fact of completely closing sessions and starting anew. Activation groups have been totally destroyed, and pointers to existing programs in QRPLOBJ are lost to your sessions.
If you now remove the explicit declarations and the behavior reverts to the same error condition, I suggest contacting IBM. I’d suspect something else is causing the problem.
Tom