SQLCODE 30 on fetch SQLRPGLE

470 pts.
Tags:
NULL values
SQL
SQLRPGLE
Hello All, 
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dcantwell
    I gave up on trying to format this question nicely... Every time I changed it, it got worse. I apologize.
    470 pointsBadges:
    report
  • philpl1jb
    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
    50,505 pointsBadges:
    report
  • deepu9321
    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.
    3,870 pointsBadges:
    report
  • Dcantwell
    Oh look, now all of a sudden my formatting is working too! Technology... :-/
    470 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following