You never can use a SELECT statement as in your embedded SQL example.
You need to return the data, i.e. read the result into host variables.
If the SELECT-Statement returns only a single row, you need an INTO clause for receiving the data:
Exec SQL SELECT .... INTO :HostVar1, :HostVar2 .... :HostVarN
From ....;
Instead of listing the (output) Host-Variables one after the other, you also can define and use a Data Structure containing all of the output fields.
If your SELECT Statement will return multiple rows, you need to declare a Cursor.
EXEC SQL Declare CursorName Cursor For SELECT .....;
Then OPEN the cursor and FETCH each row into Host-Variables or a Host-Data Structure.
After having processed all rows you have to CLOSE the cursor again.
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: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Art Tostaine, Jr.
Sent: Friday, 5 June 2026 16:31
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Read JSON direct from IFS file
Art Tostaine, Jr. <atostaine@xxxxxxxxx>
10:14 AM (15 minutes ago)
to RPG400-L-request
I cobbled this example together from Scott's examples and other sources. I can't get it to compile. I've processed JSON before with other methods, I'd like to use all SQL this time.
dcl-s p_Ifs_file char(256)
inz('/api/logs/globale/20260604/497439-GLOBALE-Out-rsp.txt');
exec sql select t.id, t.DocumentData
from
JSON_TABLE( :p_ifs_file,
'lax $.*' COLUMNS(
id VARCHAR(30) PATH '$.id',
DocumentData VARCHAR(100000) PATH '$.DocumentData
)
) as t;
My compile errors are on the SELECT line are position 10 INTO clause missing from embedded statement and the Document Data line Postition 38 attributes are not valid.
My JSON has a large base64 encoded value as DocumentData. I was planning on decoding it later. I would love to decode it in this statement.
Thanks for any help
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
