Re: JSON Fetch First Row Only

["Art Tostaine, Jr." <atostaine@xxxxxxxxx>]

I've got this working in interactive SQL but I get SQL0029 SQL0029
"Position 13 INTO clause missing from embedded statement."
when trying to use in RPG.  I don't know where it needs INTO.  Some code
SNIPPED

With First_Row
(intermid,prfxdesc,firstname,middlenme,lastname,suffixdes,birthdate
)
AS (select
intermid,prfxdesc,firstname,middlenme,lastname,suffixdes,birthdate
from namintprp
fetch first 10 rows only
)

select json_object('data':
         json_arrayagg(
           json_object(
             'ExternalId': trim(intermid),
             'Prefix': trim(PRFXDESC),
             'FirstName': trim(firstname),
             'MiddleName': trim(middlenme),
             'LastName': trim(lastname),
             'Suffix': trim(SUFFIXDES),
             'dateofBirth': trim(BIRTHDATE),
             'dateofDeath': trim(DATEDEATH)
             )
           )
         )
       )
from First_Row;

On Wed, Jan 22, 2020 at 9:36 AM Art Tostaine, Jr. <atostaine@xxxxxxxxx>
wrote:

> Thank you.  I'll try to figure out how to use a CTE.
>
> On Wed, Jan 22, 2020 at 8:35 AM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:
>
> > ...unfortunately not, as far as I can see. MariaDB allows the use of
> > OFFSET and LIMIT as part of the aggregation function but DB2 doesn't so
> > you'd need a sub-select or common table expression.
> >
> > Tim.
> >
> > ________________________________
> > From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of
> > Art Tostaine, Jr. <atostaine@xxxxxxxxx>
> > Sent: 22 January 2020 17:28
> > To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> > Subject: JSON Fetch First Row Only
> >
> > Can I use Fetch First Row Only in JSON_Object?  This is a snip of the
> > code,
> > when I test the select in IBM iACS Sql scripts, I get one record.  But my
> > STMF from this statement has the JSON many times.
> >
> >
> > exec sql select json_object('data':
> >       json_arrayagg(
> >         json_object(
> >           'ExternalId': trim(intermid),
> >           'Prefix': trim(PRFXDESC),
> >           'FirstName': trim(firstname),
> > .......
> > into :outFile
> > from namintprp
> > where nameid = '0275831'
> > fetch first row only
> > ;
> >
> > Thanks
> >
> > --
> > Art Tostaine
> > --
> > 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://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7C984eb1e52e564c1e8c1308d79f583140%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153073435789651&amp;sdata=672pPpN5I%2BGatxCcSFK5NRc5xBaEzxZ3sy4uzr8vCVs%3D&amp;reserved=0
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at
> > https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7C984eb1e52e564c1e8c1308d79f583140%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153073435789651&amp;sdata=3TKI3aTa1nR3pQFUA4CRPiT9Yv%2FmtGePBcWtz7%2Bb7RI%3D&amp;reserved=0
> > .
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link:
> > https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7C984eb1e52e564c1e8c1308d79f583140%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153073435789651&amp;sdata=ybPbtD06UhXYWmjBNE%2FAKxlXDV9%2BIL9v6ZrDdM0NYAc%3D&amp;reserved=0
> > --
> > 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@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: https://amazon.midrange.com
>
>
> --
> Art Tostaine