× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Ah right. Thank you.

So I have this. How do I specify the json_object within the Fetch next
loop? I don't think I can use SELECT again?

exec sql 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)
)
)
)
into :outFile;

I get this error:
SQL0104 Position 21 Token <END-OF-STATEMENT> was not valid. Valid tokens: ,
FROM.

Thanks, Art

On Wed, Jan 22, 2020 at 1:07 PM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Hi Art

You cannot do a SELECT on its own like this - there are a couple things
- one is to DECLARE CURSOR with this statement, then do the OPEN and FETCH.

The precompiler thinks you are trying to do a SELECT INTO statement,
which does not need the DECLARE CURSOR.

Hope that makes a little sense.
Vern

On 1/22/2020 3:01 PM, Art Tostaine, Jr. wrote:
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



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.