× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



In either way I'd always check either the SQLCODE/SQLCOD or SQLSTATE/SQLSTT
immediately after the SQL Statement.

BTW if possible prefer static SQL.
With static SQL an access plan will be stored within the (Service-)Program
Object, that can be used or at least validated with the next execution.
With dynamic SQL there are much more steps that must be executed at run
time, for example Syntax checking, converting a string into an executable
SQL statement, building an access plan by scratch (at least if the SQL
statement is executed by the CQE, with the SQE it might be possible to use
or validate an access plan stored within the SQE plan cache).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Coyle, Stephen F.
Gesendet: Tuesday, 20. October 2009 18:41
An: RPG programming on the IBM i / System i
Betreff: RE: Embedded SQL Fails on JOIN

OK...I will remove the prepare statement. Thank you.

The error is at run time.

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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.