I believe that dynamic SQL is being used because the OP mentioned that the program is generating one of six variants of this query at run time, however I do agree that the common table expression is unnecessary for the specific statement provided.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx> wrote: -----
To: "'RPG programming on the IBM i \(AS/400 and iSeries\)'" <rpg400-l@xxxxxxxxxxxx>
From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
Date: 02/05/2015 01:20AM
Subject: AW: SQL0101 in an SQLRPGLE program
Values must be specified BEFORE the SELECT-Statement and common table
expressions (WITH ...) are part of the SELECT Statement.
There is no need for dynamic SQL in your Statement. Just Replace the
Parameter Marker with your Host Variable and add EXEC SQL.
Exec SQL
values (
with t1 as (select distinct aydotc
from acbdxtr
join ayagnt
on abdbrok = ayagnt
where abdrcty in ('PR')
and abdlnbs = 'R*'
and ABDGLDT between 00000000 and 99999999)
select count(*) from t1) into :YourHostVariable;
But one Question:
Why to use a common Table expression at all?
Try the following statement:
Exec SQL
Values(Select Count(Distinct aydotc)
From abcxtr join ayagnt on abdbrok = ayagnt
Where abdrcty in ('PR')
And abdlnbs = 'R*'
And abdgldt between 0 and 9999999)
Into :yourHostVariable;
Or
Select Count(Distinct aydotc) into :YourHostVariable
From abcxtr join ayagnt on abdbrok = ayagnt
Where abdrcty in ('PR')
And abdlnbs = 'R*'
And abdgldt between 0 and 9999999;
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 [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jonathan
Mason
Gesendet: Wednesday, 04.2 2015 19:17
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL0101 in an SQLRPGLE program
Hi List
I am trying to prepare the following SQL statement in an SQLRPGLE program
and am getting the error SQL0101 SQL statement too long or complex.
with t1 as (select distinct aydotc
from acbdxtr
join ayagnt
on abdbrok = ayagnt
where abdrcty in ('PR')
and abdlnbs = 'R*'
and ABDGLDT between 00000000 and 99999999)
values (select count(*) from t1) into ?
If I strip out the "values" and "into ?" and run the statement in
interactive SQL it works fine and generates a result.
The statement is built dynamically, within a loop that will ultimately
prepare six variations. I'm wondering if the error message is a red herring
and if the error is due to reusing the same preparation ID (prpCount) each
time through the loop?
Has anybody come across this issue before or have any ideas.
Thanks in advance for any and all suggestions.
All the best
Jonathan
________________________________
Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts
Festival.
Registered Address: Ageas House, Hampshire Corporate Park, Templars Way,
Eastleigh, Hampshire, SO53 3YA Registered Number: 354568 England
Authorised by the Prudential Regulation Authority and regulated by the
Financial Conduct Authority and the Prudential Regulation Authority
This e-mail together with any attachments are intended for the addressee
only and may be private and confidential. If you are not the intended
recipient, or the person responsible for delivering it to the intended
recipient, you must not open any attachments, or copy, disclose, distribute,
retain or use this e-mail, including any attachments, in any way whatsoever;
please return it to us immediately using the reply facility on e-mail.
Consider the environment and think before you print this email.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (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.
midrange.com
