Re: dynamic json build in embedded rpg pgm

Tried it out - and of course my syntax was wrong.

values
case
when :myVal <> ''
then json_object(key 'myval' value trim(:myVal))
end;

If this case is part of some bigger json_object call, you should use ABSENT ON NULL on that outer call, because CASE always returns NULL if no WHEN clause is true and no ELSE clause exists.

HTH
Daniel

Am 12.09.2024 um 07:00 schrieb Daniel Gross <daniel@xxxxxxxx>:

Hi Jay,

I think there is more than one error in the SQL.

values
case
when :myVal <> ''
then json_object(
key => 'myval',
value => trim(:myVal)
)
else ''
end
into :myJsonVal;

Right now I'm not at my computer - but I think that this should be somehow right.

Be warned, if you leave out the else - you will need a null indicator variable or a ifnull function around that, because case without else returns Null, if no when is true.

And keep in mind, that case always only "returns" one value - so you can't return 2 function parameters.

HTH
Daniel

Btw: I will try this later, to find out if my brain is right on this.

Am 11.09.2024 um 17:26 schrieb Jay Vaughn <jeffersonvaughn@xxxxxxxxx>:

I need to only include the json field if there is a value other than blank
associated with that field's value...

for example, I set out trying to solve this by doing...

values
json_object(
case when :myVal <> ' '
then key 'myVal'
value trim(:myVal)
)

... but no go... compiler does not like this.

So on a field by field basis, how can I continue using the sql json
functions but condition which fields should be included in the json?

tia

Jay
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.