sponsored links



No announcement yet.

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

  • Filter
  • Time
  • Show
Clear All
new posts

  • TheZenbudda
    commented on 's reply
    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.

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

    Leave a comment:

  • Vectorspace
    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.

    Leave a comment:

  • B.Hauser
    What SQLCODE or SQLSTATE are you getting instead of 02000?

    Leave a comment:

  • 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:


    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)


    ctl-opt dftactgrp(*noactgrp(*new)                                                          

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

    customer 1;                                                                                

    exec sql                                                                                     
    'mykey' value 'myvalue'                                                              
    'myarray' value                                                                      
    'customerNumber' value Customer_Number                                        
    'paymentSequence' value Payment_Sequence                                      
    'paymentAmount' value Payment_Amount                                          
    into :jsondata                                                                             
             from mylib
             where customer_number 
    = :customer                                                          

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

    inlr = *on;                                                                                 
    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).