|
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 SQL0029wrote:
"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>
midrange-l@xxxxxxxxxxxxxxxxxx>
...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 <
mySubject: 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
https://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=3BxbVYtgD3aeVhXwD6GZnVLlwuDWs1r1rDcVrM8eHkQ%3D&reserved=0STMF 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://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=o4%2FdUSUokX9foPb9Gg%2BAM7kJ4yNShKV%2BR%2FSYtMK7diU%3D&reserved=0or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=xc%2FtEbsNWTA5ajSqXMJU8WDVXocsOoyi1Nnov6KSatk%3D&reserved=0.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
--
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://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=3BxbVYtgD3aeVhXwD6GZnVLlwuDWs1r1rDcVrM8eHkQ%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=o4%2FdUSUokX9foPb9Gg%2BAM7kJ4yNShKV%2BR%2FSYtMK7diU%3D&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://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=xc%2FtEbsNWTA5ajSqXMJU8WDVXocsOoyi1Nnov6KSatk%3D&reserved=0
--
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://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=3BxbVYtgD3aeVhXwD6GZnVLlwuDWs1r1rDcVrM8eHkQ%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=o4%2FdUSUokX9foPb9Gg%2BAM7kJ4yNShKV%2BR%2FSYtMK7diU%3D&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://eur05.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7Cfb6d744889f9458f392308d79f818d22%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153251068390397&sdata=xc%2FtEbsNWTA5ajSqXMJU8WDVXocsOoyi1Nnov6KSatk%3D&reserved=0
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
