Re: sql declare/select/fetch number of elements.

[rick baird <rick.baird@xxxxxxxxx>]

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.