Syntax of the VALUES ... INTO statement is not correct.
Try to change your embedded SQL Statement as follows:
exec sql
With elm (erpRef) as (select json_object
('ServiceID' VALUE trim(s.ServiceID),
'ERPReferenceID' VALUE trim(i.RefCod) )
FROM acelib/PADIMH I
INNER JOIN acelib/PADGUIDS G ON G.REFCOD = I.REFCOD
INNER JOIN acelib/PADSERV S ON S.GUID = G.GUID
WHERE G.XMLTYPE = 'Service')
, arr (arrDta) as (values json_array (
select erpRef from elm format json))
, erpReferences (refs) as ( select json_object ('erpReferences' :
arrDta Format json) from arr)
, headerData (hdrData) as (select json_object(
'InstanceName' : trim(Cntry) )
from acelib/padxmlhdr
where cntry = 'US')
select json_object('header' : hdrData format json,
'erpReferenceData' value refs format json)
INTO :OutFile
from headerData, erpReferences;
or
exec sql
VALUES (
With elm (erpRef) as (select json_object
('ServiceID' VALUE trim(s.ServiceID),
'ERPReferenceID' VALUE trim(i.RefCod) )
FROM acelib/PADIMH I
INNER JOIN acelib/PADGUIDS G ON G.REFCOD = I.REFCOD
INNER JOIN acelib/PADSERV S ON S.GUID = G.GUID
WHERE G.XMLTYPE = 'Service')
, arr (arrDta) as (values json_array (
select erpRef from elm format json))
, erpReferences (refs) as ( select json_object ('erpReferences' :
arrDta Format json) from arr)
, headerData (hdrData) as (select json_object(
'InstanceName' : trim(Cntry) )
from acelib/padxmlhdr
where cntry = 'US')
select json_object('header' : hdrData format json,
'erpReferenceData' value refs format json)
from headerData, erpReferences
) INTO :OutFile;
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!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Baeten,
John
Sent: Donnerstag, 25. Juni 2020 04:13
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQLRPGLE & JSON CTE Statements -101 Error
This does work using the ACS Run SQL Scripts, But doesn't seem to like it in
the program. I am not sure if the issue is with this SQL or it is is a
setup issue on our system.
RPGLE:
dcl-s OutFile sqltype(dbclob_file);
xfil_tofile = '/dp11test/ServiceID-REFCODJ.json';
Clear OutFile;
OutFile_Name = %TrimR(XFil_ToFile);
OutFile_NL = %Len(%TrimR(OutFile_Name));
OutFile_FO = IFSFileCreate;
OutFile_FO = IFSFileOverWrite;
exec sql
With elm (erpRef) as (select json_object
('ServiceID' VALUE trim(s.ServiceID),
'ERPReferenceID' VALUE trim(i.RefCod) )
FROM acelib/PADIMH I
INNER JOIN acelib/PADGUIDS G ON G.REFCOD = I.REFCOD
INNER JOIN acelib/PADSERV S ON S.GUID = G.GUID
WHERE G.XMLTYPE = 'Service')
, arr (arrDta) as (values json_array (
select erpRef from elm format json))
, erpReferences (refs) as ( select json_object ('erpReferences' :
arrDta Format json) from arr)
, headerData (hdrData) as (select json_object(
'InstanceName' : trim(Cntry) )
from acelib/padxmlhdr
where cntry = 'US')
VALUES (
select json_object('header' : hdrData format json,
'erpReferenceData' value refs format json)
from headerData, erpReferences )
INTO :OutFile;
Thanks,
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles
Wilt
Sent: Wednesday, June 24, 2020 5:42 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQLRPGLE & JSON CTE Statements -101 Error
Caution: This email message originated from outside of the organization. DO
NOT CLICK on links or open attachments unless you recognize the sender and
know the content is safe. If you think it is suspicious, please report as
suspicious.
Need to see the code...
Charles (yes the same one)
On Wed, Jun 24, 2020 at 4:22 PM Baeten, John <John.Baeten@xxxxxxxxxx> wrote:
I am able to run the SQL statement in ACS Run SQL Scripts, but when I
add those same statements to the SQLRPGLE program I get the following
SQLCOD error. The SQLSTATE code is 54011 which states: Too many
columns were specified for a table, view, or table function.
Can someone tell me if there is something that needs to be turned on
our system to be able to run these in an SQLRPGLE program?
-101 THE STATEMENT IS TOO LONG OR TOO COMPLEX
Explanation
DB2(r) cannot process the statement because it exceeds the system
limits for length or complexity. Enabling parallelism will increase
the complexity of the statement.
System action
DB2 cannot process the statement.
Programmer response
Divide the statement into shorter or less complex SQL statements.
If the statement enables parallelism, try disabling parallelism. You
may do this by using the DEGREE(1) bind option for static SQL, or by
setting the CURRENT DEGREE special register to '1' for dynamic SQL.
CONFIDENTIALITY NOTICE: This e-mail communication and any attachments
may contain proprietary and privileged information for the use of the
designated recipients named above. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all
copies of the original message.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinf
o/rpg400-l__;!!Nkc5UzxO!-EJiRLxBM4edg9G-svJhGtQnOfJM4wMKUvhnVSAA7Ktjrp
g2UaTX6hGWWn5u68xvnA$ or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/rpg400-l__;!!Nkc5Uz
xO!-EJiRLxBM4edg9G-svJhGtQnOfJM4wMKUvhnVSAA7Ktjrpg2UaTX6hGWWn5nGcxzzA$ .
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!Nkc5UzxO!-
EJiRLxBM4edg9G-svJhGtQnOfJM4wMKUvhnVSAA7Ktjrpg2UaTX6hGWWn4lxEl4iA$
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/rpg4
00-l__;!!Nkc5UzxO!-EJiRLxBM4edg9G-svJhGtQnOfJM4wMKUvhnVSAA7Ktjrpg2UaTX6hGWWn
5u68xvnA$
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/rpg400-l__;!!Nkc5Uz
xO!-EJiRLxBM4edg9G-svJhGtQnOfJM4wMKUvhnVSAA7Ktjrpg2UaTX6hGWWn5nGcxzzA$ .
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!Nkc5UzxO!-EJiRLx
BM4edg9G-svJhGtQnOfJM4wMKUvhnVSAA7Ktjrpg2UaTX6hGWWn4lxEl4iA$
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
