Re: sql to scan and substring

Missed an edit when pasting the SQL (and it looked ugly), so here's a cleaner, tighter version:

Select RtnResponse, Case RtnResponse When 'Paid' Then 'Allowed' When 'Decline' Then 'Not Allowed' Else 'I Dont Know' End, jd.*

From JSONDATAPF jd,

Lateral (Values Cast(LogData As Char(4000))) As c (LineData),
Lateral (Values SubString(LineData, Locate('"status":"', LineData) + 10, Locate('",', LineData, Locate('"status":"', LineData) + 10) - (Locate('"status":"', LineData) + 10))) As rp (RtnResponse)
;

Herb.


________________________________
From: Herb Rea via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Sent: Tuesday, February 3, 2026 11:46 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Herb Rea <herbr@xxxxxxxxxxxxxxxxxxx>
Subject: Re: sql to scan and substring

Here's one method to extract the attribute of "status":

1.
If the JSON data is unreadable, it might be because it's considered a BLOB, so re-Cast it as a CHAR (make it big!!)
2.
Use Laterals to keep the reset of the SQL clean (terminate the Lateral clause with a comma). And Laterals allow you to rename returned values
3.
Locate and Substring work just fine for moving targets but you need to allow for the length of the search string to retrieve the response
4.
If you want to simply the final Substring, use an additional lateral to save off the location of "status":"
5.
My example assumes that the JSON is using doublequote - colon-doublequote and doublequote-comman-doublequote formatting
6.
jd = JSON data from physical file, c = re-Cast-ed JSON field, rp = parse response


Select RtnResponse,
Case RtnResponse When 'Paid' Then 'Allowed'
When 'Decline' Then 'Not Allowed'
Else 'I Dont Know' End,
jd.*
From JSONDATAPF jd,
Lateral (Values Cast(LogData As Char(4000)) From QSys2.QSqPTabl) As c (LineData),
Lateral (Values SubString(LineData,
Locate('"status":"', LineData) + 10,
Locate('",', LineData, Locate('"status":"', LineData) + 10) - (Locate('"status":"', LineData) + 10))) As rp (RtnResponse)
;


Herbert Rea | Mason Associates, Inc. | herbr@xxxxxxxxxxxxxxxxxxx
Falmouth, Maine | 207.347.3557 | Lewiston, Maine | 207.212.8498



________________________________
From: Jim Franz <franz9000@xxxxxxxxx>
Sent: Tuesday, February 3, 2026 9:11 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: sql to scan and substring

[You don't often get email from franz9000@xxxxxxxxx. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

I'm trying to select the value from a log file which holds part of large
json rows, but the json incomplete and not readable so the "status" never
in same position. I need the value of status.or even the whole string
"status":"Paid"
(scan and substring may be wrong description)
sample data
"status":"Paid","allow
],"status":"Declined",

I can picture this in RPGLE but trying to improve my sql...
Found solutions online but not DB2.

Jim Franz
--
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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cc596779af28e46b8ce2208de6343dfb1%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C639057340327813203%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C4000%7C%7C%7C&sdata=UsxcpwRYIKDsnHrmByC2SP9dhzThg3qqgVrSC6%2FnY8I%3D&reserved=0<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cc596779af28e46b8ce2208de6343dfb1%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C639057340327847325%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C4000%7C%7C%7C&sdata=dZsfWUW4z3Vf%2B1Ns%2B8exDs2eBt1nEJ7lTT%2FMrPyZjRs%3D&reserved=0><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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cc596779af28e46b8ce2208de6343dfb1%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C639057340327869382%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C4000%7C%7C%7C&sdata=2xRPt4Q9kQDzPFuKvZ5G7JlG9H9pCTa3dL9wB4xmOys%3D&reserved=0<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cc596779af28e46b8ce2208de6343dfb1%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C639057340327889524%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C4000%7C%7C%7C&sdata=shlrnyj16Jzr9sYKGAHKMcOKIE68f%2BgcVBoH5vckapw%3D&reserved=0><https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cc596779af28e46b8ce2208de6343dfb1%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C639057340327909166%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C4000%7C%7C%7C&sdata=5ugeY7jhabHwXmAJjWxA%2FDN6ome1aul0J9uNYb684Jk%3D&reserved=0<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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cc596779af28e46b8ce2208de6343dfb1%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C639057340327926821%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C4000%7C%7C%7C&sdata=6EeXzwkfrztIheYUGxHp3qr7drfVTgbrG3K6LPbg2uQ%3D&reserved=0<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.