SQLRPGLE & JSON_OBJECT CTE Statements -101 Error

Question

This program compiles correctly, we are on V7R3 - but when running it receives an SQLCOD of -101 and an SQLSTATE code is 54011 which states: Too many columns were specified for a table, view, or table function. This is a very small JSON that is being created so I do not think that is the issue.

The RPGLE code:

dcl-s OutFile sqltype(dbclob_file);    
  xfil_tofile = '/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 PADIMH I                                     
             INNER JOIN PADGUIDS G ON G.REFCOD = I.REFCOD      
             INNER JOIN 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 padxmlhdr                                              
   where cntry = 'US')                                                
                                                                      
      VALUES (                                                        
     select json_object('header' : hdrData format json,               
    'erpReferenceData' value refs format json)                        
     from headerData, erpReferences )                                 
INTO  :OutFile;

Any help with this would be very much appreciated, this is our first attempt at creating JSON for sending and have not experienced this issue before.

Thanks, John

There must be a SQL0101 in the joblog that gives you more precise information about the reason why it fails — nfgl, Jun 26 '20 at 08:53
how many records does `select erpRef from elm` return ? and does `values json_array ( select erpRef from elm format json)`fail too ? — nfgl, Jun 26 '20 at 08:54

score 0 · Accepted Answer · answered Jul 01 '20 at 01:38

I am sorry for the delay in getting back to this issue. It has been corrected, the issue was with the "values" statement.
This is the correct code needed to make it work correctly:

Select json_object('header' : hdrData format json,
'erpReferenceData' value refs format json) 
INTO  :OutFile                       
From headerData, erpReferences )

SQLRPGLE & JSON_OBJECT CTE Statements -101 Error

1 Answers1