IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2019-09-18T14:48:18Z by DickjAZ
DickjAZ
DickjAZ
2 Posts

Pinned topic SQL0312 member of host structure is a structure

‏2019-09-17T20:35:46Z | sqlrpgle

I've searched high and low for how to define a sub data structure to receive JSON data. (JSON_TABLE) This definately

works on a single level.  When I add one with a Parent/Child relationship I am having problems defining the receiving DS.

QL0312 Position 43 Variable JSONFIELDS not defined or not usable. Reason: A member of the host structure is a structure.
 // Individual Unit results
        dcl-ds UNITS_T Template;
           UNIT Char(6);
           WEIGHT Int(5) ;
           DESCR VARCHAR(128);
           BULK CHAR(5);
           LIMITQTY CHAR(5);
           PIH CHAR(5) ;
           MRNPOL CHAR(5);
        End-Ds;

        dcl-ds jsonFields qualified ;
           ID  Int(5);
           DRIVER  Char(6);
           SURVY_STRT CHAR(20);
           SURVY_END CHAR(20);
           IMPORT_DT CHAR(20);
           RESULTS CHAR(10);
           UNITS LIKEDS(UNITS_T) DIM(20);
        end-ds;                                
        
        dcl-ds result qualified;
           success ind;
           errmsg varchar(500);
           jsonArray likeds(jsonFields) dim(9999);
        end-ds;                                      
 
======================================================================
  EXEC SQL DECLARE C1 CURSOR FOR
             SELECT *
               FROM JSON_TABLE(:JSON, '$'  // read the JSON data from a string
                        COLUMNS (
                                 ID INT PATH '$.id',
                                 DRIVER CHAR(6) PATH '$.userDriverId',
                                 SURVY_STRT CHAR(20) PATH '$.dateReviewStarted',
                                 SURVY_END CHAR(20) PATH '$.dateReviewEnded',
                                 IMPORT_DT CHAR(20) PATH '$.dateImported',
                                 RESULTS CHAR(10) PATH '$.results',
                                 NESTED PATH '$.units[*]'
                                   COLUMNS (
                                    UNIT CHAR(6) PATH '$.un',
                                    WEIGHT INT PATH '$.weight',
                                    DESCR VARCHAR(128) PATH '$.desc',
                                    BULK CHAR(5) PATH '$.isBulk',
                                    LIMITQTY CHAR(5) PATH '$.isLimitedQuantity',
                                    PIH CHAR(5) PATH '$.isPIH',
                                    MRNPOL CHAR(5) PATH '$.isMarinePollutant'
                                     )
                       )) AS X;

        EXEC SQL OPEN C1;

        EXEC SQL FETCH NEXT FROM C1 INTO :JSONFIELDS ;   


        dow sqlstt='00000' or %subst(sqlstt:1:2)='01';
          lastelem += 1;
          // Store Datastructure values in next element of 'return array'
          result.jsonArray(lastelem) = jsonFields;
          // here we could do something with each row of data ie: write to file
          // or some other business logic.
          EXEC SQL FETCH NEXT FROM C1 INTO :JSONFIELDS ;
        enddo;                       

 

  • B.Hauser
    B.Hauser
    128 Posts

    Re: SQL0312 member of host structure is a structure

    ‏2019-09-18T05:28:35Z  

    You cannot use nested data structures in composition with embedded SQL. JSON_TABLE will return a table, i.e. all information of the header (multiple times) and the details only one time. So you need to change your data structure.

    Just Execute your JSON_TABLE Select Statement interactively and look at the result.

    Birgitta

     

  • DickjAZ
    DickjAZ
    2 Posts

    Re: SQL0312 member of host structure is a structure

    ‏2019-09-18T14:48:18Z  

    I realized that the header is carried with each line in the nested path.  I added the individual columns to the jsonFields DS and it compiled.

    Thank you for your help.