Re: sql declare/select/fetch number of elements. -- RPG400-L

Hi Chris - when I run in debug and switch to SQL Output View, I see
the SQL moves and it is indeed ignoring the xErrInd in the data
structure.

I guess I can hard code the definition, but why? the 'liked' fields
are all in the same file, some alpha, some numeric, why is it ignoring
just the one with max()?

I'll hard code the definition for now, i don't have time to keep
messing with this, but if anyone has a clear answer, I would still
love to hear it.

On Fri, May 11, 2012 at 10:08 AM, Hiebert, Chris
<chris.hiebert@xxxxxxxxxxxxxx> wrote:

My guess is that the pre-compiler is just ignoring some subfields
because it doesn't know how to define them. I had a similar problem some
time ago when using LIKE definitions with SQL. (Running V5R4).

When you compile the source member change the TOSRCFILE option to
somewhere other than QTEMP.
I.e. TOSRCFILE(YOURLIB/QSQLTEMP1)

Then look in that source member and see how the system is defining your
data structure. You should see some SQL_##### fields where the comment
matches the name from your data structure subfields. These should be
listed in order. If anything is missing then try to change the
definition on your C3ds subfield.

Here is an example:

D CursorData DS QUALIFIED INZ

D Cpcrule LIKE(RLCPCRULE)

D Sequence LIKE(RHSEQ)

D TokenId LIKE(TKTOKENID)

D TokType LIKE(TKTOKTYPE)

D TokenQv LIKE(TKTOKENQV)

Precompiler's generated DS:
D SQL_00059 129 133P 0
CURSORDATA.CPCRULE
D SQL_00060 134 138P 0
CURSORDATA.SEQUENCE
D SQL_00061 139 170A VARYING
CURSORDATA.TOKENID
D SQL_00062 171 171A
CURSORDATA.TOKTYPE
D SQL_00063 172 267A VARYING
CURSORDATA.TOKENQV

Chris Hiebert
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rick baird
Sent: Friday, May 11, 2012 7:53 AM
To: RPG programming on the IBM i / System i
Subject: sql declare/select/fetch number of elements.

Hey all. I'm getting an SQL0030 - Number of host variables less than
result values - error on the following code.

I'm counting eight variables in the data structure and eight in the
select statement. I run the sql interactively and I get eight columns.
x3Errind is blank after each fetch. the rest of the field values are as
expected, so for some reason it doesn't like the last field in my DS.
No other errors and no warnings on that data structure in my compile
listing.

Any ideas would be greatly appreciated! Thanks!

D C3ds ds inz
D x3CMPY like(ICMPY)
D x3JRNL like(IJRNL)
D x3BAT like(IBAT )
D x3YYPP like(IYYPP)
D x3DAMT 11 2
D x3CAMT 11 2
D x3CNT 8 0
D x3ErrInd like(IERRIND)

C/exec sql
C+ Declare C3
C+ cursor for
C+ select ICMPY,
C+ IJRNL,
C+ IBAT,
C+ IYYPP,
C+ cast(sum(IDAMT) as dec(11,2)),
C+ cast(sum(ICAMT) as dec(11,2)),
C+ cast(count(*) as dec(8,0)),
C+ max(IERRIND)
C+ from JRIMMAST
C+ group by
C+ ICMPY,
C+ IJRNL,
C+ IYYPP,
C+ IBAT
C+ order by
C+ ICMPY,
C+ IJRNL,
C+ IYYPP,
C+ IBAT
C/end-exec
---
C/exec sql
C+ open C3
C/end-exec
---
C/exec sql
C+ fetch C3
C+ into :C3ds
C/end-exec

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.