RE: Embedded SQL Fails on JOIN

This what I've found...these snippets were copied directly from the compile listing...
The code inserted by the pre-compiler is only returning 6 fields but the select statement is calling for 7.
The error message now makes sense to me. But I still do not understand why it is dropping the last field.
Does anyone have any ideas why the compiler would do this?

D IPDs Ds INZ
D IP$BC Like( My57755U.Re$BC )
D IPAn8 Like( My57755U.ReAn8 )
D IPEftjx Like( My57755U.ReEftjx )
D IPEnddx Like( My57755U.ReEnddx )
D IPMcu Like( My57755U.ReMcu )
D IP$OPS Like( My57755U.Re$OPS )
D IPRp17 Like( My0006.McRp17 )

052900 //****SQL
053000 //****clare ReadCsr CURSOR FOR
053100 //****elect Re$BC, ReAN8, ReEftjx,
053200 //**** ReEnddx, ReMcu, Re$OPS, Mcrp17
053300 //**** From Qtemp/F57755U
053400 //**** Left Outer Join F0006
053500 //**** on TRIM(ReMcu) = TRIM(McMcu)
053600 //**** Order by McRP17, Re$BC, ReAn8 ;

056800 //****SQL
056900 //****tch Next From ReadCsr Into :IPDs :NullInds ;
056900 /END-FREE
056900 C Z-ADD -4 SQLER6
056900 C CALL SQLROUTE
056900 C PARM SQLCA
056900 C PARM SQL_00012
056900 C SQL_00015 IFEQ '1'
056900 C . EVAL IP$BC = SQL_00017
056900 C . EVAL IPAN8 = SQL_00018
056900 C . EVAL IPEFTJX = SQL_00019
056900 C . EVAL IPENDDX = SQL_00020
056900 C . EVAL IPMCU = SQL_00021
056900 C . EVAL IP$OPS = SQL_00022
056900 C . EVAL NULLINDS(1) = SQL_00023
056900 C . EVAL NULLINDS(2) = SQL_00024
056900 C . EVAL NULLINDS(3) = SQL_00025
056900 C . EVAL NULLINDS(4) = SQL_00026
056900 C . EVAL NULLINDS(5) = SQL_00027
056900 C . EVAL NULLINDS(6) = SQL_00028
056900 C END

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, October 20, 2009 2:13 PM
To: RPG programming on the IBM i / System i
Subject: Re: Embedded SQL Fails on JOIN

Stephen,

Check for silly stuff...like a DS subfile wiyth a S that makes it a
standalone field instead of a subfield.

Charles

On Tue, Oct 20, 2009 at 1:30 PM, Coyle, Stephen F. <SCoyle@xxxxxxxxxxxxx> wrote:

I removed the prepare and modified the declare accordingly.

No change on the runtime error. I'm stumped.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, October 20, 2009 12:35 PM
To: RPG programming on the IBM i / System i
Subject: Re: Embedded SQL Fails on JOIN

First of all,

There's no reason for the code you're showing to be dynamic.

 exec SQL
 declare C1 cursor for
   Select
       Re$BC, ReAN8, ReEftjx,
       ReEnddx, ReMcu, Re$OPS, Mcrp17
   From Qtemp/F57755U
          Left Outer Join F0006
             on TRIM(ReMcu) = TRIM(McMcu)
   Order by McRP17, Re$BC, ReAn8;

exec SQL
 open C1;

exec SQL
  fetch next from C1 into :IPDs :NullInds;

The above compile fine for me, are you getting a compile time or a
run time error?

Charles

On Tue, Oct 20, 2009 at 12:04 PM, Coyle, Stephen F.
<SCoyle@xxxxxxxxxxxxx> wrote:

Hi All,

       I have an embedded SQL statement that fails when I select fields
from a secondary file. It works fine when I only select fields from the
primary file. Here is the code and message received. MCRP17 is from the
secondary.

Thanks in advance...
- Steve

D  MySQL          S           2000    Varying

                                    INZ('Select -

                                      Re$BC, ReAN8, ReEftjx, -

                                      ReEnddx, ReMcu, Re$OPS, Mcrp17  -

                                      From Qtemp/F57755U -

                                      Left Outer Join F0006 -

                                      on TRIM(ReMcu) = TRIM(McMcu) -

                                      Order by McRP17, Re$BC, ReAn8')


D IPDs            Ds                  INZ
D   IP$BC                             Like( My57755U.Re$BC   )
D   IPAn8                             Like( My57755U.ReAn8   )
D   IPEftjx                           Like( My57755U.ReEftjx )
D   IPEnddx                           Like( My57755U.ReEnddx )
D   IPMcu                             Like( My57755U.ReMcu   )
D   IP$OPS                            Like( My57755U.Re$OPS  )
D   IPRp17                            Like( My0006.McRp17    )

D NullInds        s              5i 0 dim(7)

  EXEC SQL
     Prepare S1 from :MySQL ;

  EXEC SQL
     Declare ReadCsr CURSOR FOR S1 ;

  EXEC SQL
     Open  ReadCsr ;

  EXEC SQL
     Fetch Next From ReadCsr Into :IPDs :NullInds ;


Message . . . . :   Number of host variables less than result values.

SQLSTT = '01503' The number of result columns is larger than the number
of host variables provided.

--
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.

--
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.

--
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.