JSON to BLOB IFS file compile error

We need to send JSON data from our IBMi V7.3 using an API process to another system. This API will accept a JSON file that will be created in our IFS directory.



**Please note this is a very simple JSON, but from what I have read on the IBMi website, we should use CTE's as they will be helpful for more complex JSON file creations. So, yes, this one would not need the CTE's as current - but knowing how to do this will help in creating the other JSON's that are required, which will have many different files needed to create the single JSON file.



The requested JSON needs to be in this form:

{

"header": {

"InstanceName": "US"

},

"erpReferenceData": {

"erpReferences": [

{

"ServiceID": "fb16e421-792b-4e9c-935b-3cea04a84507",

"ERPReferenceID": "J0000755"

},

{

"ServiceID": "7d13d907-0932-44c0-ad81-600c9b97b6e5",

"ERPReferenceID": "J0000756"

}

]

}

}



The below SQL works in the ACS Run SQL Scripts, with no error message.



With x as (

select json_object(

'InstanceName' : trim(Cntry) ) objHeader

from xmlhdr where cntry = 'US'),



y as (

select json_object(

'ServiceID' VALUE S.ServiceID,

'ERPReferenceID' VALUE I.RefCod ) objRef

FROM IMH I

INNER JOIN GUIDS G ON G.REFCOD = I.REFCOD

INNER JOIN SERV S ON S.GUID = G.GUID

WHERE G.TYPE = 'Service')



select json_object('header' : objHeader Format json ,

'erpReferenceData' : json_object(

'erpReferences' VALUE

JSON_ARRAYAGG(

objRef Format json)))

from x

LEFT OUTER JOIN y ON 1=1

group by objheader;





Adding this to a SQLRPGLE program I get the following error:

SQL0122: Position 41 Column OBJHEADER or expression in SELECT list not valid.

** See below for the Program's SQL, as it is different because it puts the created JSON into a BLOB file on the IFS.



One thing to note the file XMLHDR only has one record - and does have additional fields that we will be sending like phone number and email address. This was the only way we could figure out how to get the JSON to be created using our "header" file which is needed inside the JSON.



dcl-s OutFile sqltype(dbclob_file);



dcl-s IFSFileRead zoned(2:0) inz(02);

dcl-s IFSFileCreate zoned(2:0) inz(08);

dcl-s IFSFileOverWrite zoned(2:0) inz(16);

dcl-s IFSFileAppend zoned(2:0) inz(32);





xfil_tofile = '/Test/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 x as (

select json_object(

'InstanceName' : trim(Cntry) ) objHeader

from xmlhdr

where cntry = 'US'),



y as (

select json_object(

'ServiceID' VALUE S.ServiceID,

'ERPReferenceID' VALUE I.RefCod) oOjRef

FROM IMH I

INNER JOIN GUIDS G ON G.REFCOD = I.REFCOD

INNER JOIN SERV S ON S.GUID = G.GUID

WHERE G.XMLTYPE = 'Service')



VALUES (

select json_object('header' : objHeader Format json ,

'erpReferenceData' : json_object(

'erpReferences' VALUE

JSON_ARRAYAGG(

ObjRef Format json)))

from x

LEFT OUTER JOIN y ON 1=1

Group by objHeader)

INTO :OutFile;



Any help with this issue would be great, we are not SQL experts here and have tried some things but nothing fixed this issue. Could there be an issue with the CTE's used for creating the JSON? But if this is the case, why would it work with the Java emulator? Although that is not putting the data into an IFS file for sending. Also any pointers from someone else that has done something similar, maybe some input on if we are going the correct way on this - or if we should be trying something different.



Thanks,

John


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.