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__;!!Nkc5UzxO!-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/rpg400-l__;!!Nkc5UzxO!-EJiRLxBM4edg9G-svJhGtQnOfJM4wMKUvhnVSAA7Ktjrpg2UaTX6hGWWn5u68xvnA$
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__;!!Nkc5UzxO!-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$