ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQLRPGLE json_arrayagg() not causing SQLSTT to be 02000 when no data found

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLRPGLE json_arrayagg() not causing SQLSTT to be 02000 when no data found

    I'm finding that when I do not get results from a json_arrayagg(), it does not force sqlstt to be 02000.

    Here is some code:

    PHP Code:

    **BUILD TABLE AND INSERT ROWS**

    reate table mylib.mytable(
        
    Customer_Number  for column CUSTOMER   numeric(10,0not null default 0
      
    Payment_Sequence for column PAYMENTSEQ numeric(10,0not null default 0
      
    Payment_Amount   for column PAYMENTAMT numeric(10,2not null default 0
      
    primary key(CUSTOMER,PAYMENTSEQ)
    )
    rcdfmt rmytable;


    insert into mylib.mytable(customer,paymentseq,paymentamt)
    values(12345,1,12.34),(12345,2,55.55),(12345,3,44.44);


    **
    SQLRPGLE PROGRAM**

           
    ctl-opt dftactgrp(*noactgrp(*new)                                                          
                   
    option(*nodebugio:*srcstmt);                                                         

           
    dcl-s customer zoned(10:0inz;                                                              
           
    dcl-s nodata ind inz;                                                                        
           
    dcl-s jsondata char(32704inz;                                                              

           
    customer 1;                                                                                

           
    exec sql                                                                                     
             select                                                                                     
               json_object
    (                                                                             
                   
    'mykey' value 'myvalue'                                                              
                 
    'myarray' value                                                                      
                    json_arrayagg
    (                                                                      
                      
    json_object(                                                                      
                          
    'customerNumber' value Customer_Number                                        
                        
    'paymentSequence' value Payment_Sequence                                      
                        
    'paymentAmount' value Payment_Amount                                          
                      
    )                                                                                 
                    )                                                                                   
               )                                                                                        
             
    into :jsondata                                                                             
             from mylib
    .mytable                                                                        
             where customer_number 
    = :customer                                                          
             
    ;                                                                                          

           if 
    sqlcod <> or sqlstt '02000';                                                          
             
    nodata = *on;                                                                              
           endif;                                                                                       

           *
    inlr = *on;                                                                                 
           return; 
    Am I doing something wrong or is this intentional? I'm having to wrap a coalesce around my json_arrayagg() to capture a "no data found" scenario (i don't want null as the response in the jsondata variable).



  • #2
    What SQLCODE or SQLSTATE are you getting instead of 02000?

    Comment


    • #3
      I would say it's correct. In the same way that count(*) or listagg() aggregate functions return output values of 0/blank if there are no records.

      With JSON_OBJECT() and JSON_ARRAY() there is a keyword you can add to control behaviour if the entry is null. ABSENT ON NULL will omit the item entirely, and NULL ON NULL will have it return null. But if you want something specific in a null scenario, then coalesce (I prefer ifnull) is good.

      Comment


      • TheZenbudda
        TheZenbudda commented
        Editing a comment
        In case anyone tries the coalesce trick, be mindful that the result will be a string instead of a jsonarray. I couldn't figure out how to use "is json data" with coalesce.

    • #4
      Originally posted by B.Hauser View Post
      What SQLCODE or SQLSTATE are you getting instead of 02000?
      SQLCOD = 0 and SQLSTT = '00000'

      Comment

      Working...
      X