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


  1.  Home
  2. MIDRANGE-L
  3. January 2020

RE: JSON Fetch First Row Only

Your SELECT statement is inclomplete! Where is your FROM CLAUSE

The correct syntax for a SELECT ... INTO is:
Exec SQL
SELECT ......
INTO :YourHostVariable1,:YourHostVariable2, .... :YourHostVariableN
FROM ...
WHERE ...
And so on

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


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


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Art
Tostaine, Jr.
Sent: Mittwoch, 22. Januar 2020 22:25
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: JSON Fetch First Row Only

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%2Flist
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7
C984eb1e52e564c1e8c1308d79f583140%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7
C1%7C0%7C637153073435789651&amp;sdata=672pPpN5I%2BGatxCcSFK5NRc5xBaEzx
Z3sy4uzr8vCVs%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%2Farch
ive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7C984eb1e52e564c1e8c
1308d79f583140%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C6371530734
35789651&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%2Famaz
on.midrange.com&amp;data=02%7C01%7C%7C984eb1e52e564c1e8c1308d79f583140
%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153073435789651&amp;s
data=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



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