Re: Embedded SQL Fails on JOIN

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.