Re: Help with building a nested JSON command

[a4g atl <a4ginatl2@xxxxxxxxx>]

The closing param is on my statement but is not shown here. Trying to
configure json_array.
Darryl.


On Tue, Aug 12, 2025 at 12:38 PM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> You have 2 (nested) Objects, but only a single closing parenthesis.
> IMHO you have to set a closing parenthesis after smnam.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
> Modernization – Education – Consulting on IBM i
> Database and Software Architect
> IBM Champion since 2020
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> "Train people well enough so they can leave, treat them well enough so they
> don't want to. " (Richard Branson)
> "Learning is experience … everything else is only information!" (Albert
> Einstein)
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of a4g
> atl
> Sent: Tuesday, 12 August 2025 18:20
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Subject: Help with building a nested JSON command
>
> I am trying to build a SQL command to be used in a webservice. The command
> will have nested JSON objects.
>
> I am unable to figure how to structure the statement. I have viewed a
> number
> of examples, but they are all different and fail to run when I try them.
>
> Here is my example:
>
> Select json_object(
> key 'CMSNumber' value q100.kcms# -- value "CMSNumber"
> , key 'EnteredBy' value q100.kendt -- value "EnteredBy"
> , key 'Currency' value
> case when ccrcd = '' then 'USD'
> else ccrcd
> end
> -- this is the object within the object
> , 'ErrorUsers' value json_object (
> key 'User' value ke1usr absent on null
> , key 'User' value ke2usr absent on null , key 'User' value ke3usr absent
> on
> null , key 'User' value ke4usr absent on null , key 'User' value ke5usr
> absent on null
> )
>
> , key 'SalesmanNumber' value ksalno
> , key 'SalesmanName' value smnam
>
> from ....
>
> Can anyone assist with what I am doing wrong?
> Thanks
> Darryl Freinkel
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.