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:
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).
Here is some code:
PHP Code:
**BUILD TABLE AND INSERT ROWS**
reate table mylib.mytable(
Customer_Number for column CUSTOMER numeric(10,0) not null default 0
, Payment_Sequence for column PAYMENTSEQ numeric(10,0) not null default 0
, Payment_Amount for column PAYMENTAMT numeric(10,2) not 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(*no) actgrp(*new)
option(*nodebugio:*srcstmt);
dcl-s customer zoned(10:0) inz;
dcl-s nodata ind inz;
dcl-s jsondata char(32704) inz;
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 <> 0 or sqlstt = '02000';
nodata = *on;
endif;
*inlr = *on;
return;
Comment