First before you try to embed the SQL you have to create and test the SQL.
So, you have 3 columns with filter information, example: OrderNo, CustNo,
ItemNo
These filter values can be passed or not. If not, you just read everything,
for example all Orders. If multiple are keys are passed, let's say CustNo
and ItemNo only the rows with the specified CustNo and ItemNo are returned.
If so, you simply handle this in a where condition.
Your Statement may have the following WHERE Condition:
SELECT ...
FROM ...
WHERE OrderNo = Case When :HostOrderNo > 0 Then :HostOrderNo Else OrderNo
End
and CustNo = Case When :HostCustNo > 0 Then :HostCustNo Else CustNo
End
and ItemNo = Case When :HostItemNo > 0 Then :HostItemNo Else ItemNo
End
ORDER BY ...
... alternatively you could also create the SQL Statement dynamically at
runtime with exactly the where conditions you need. ... but it is harder to
code.
... and please NEVER EVER Specify a DDS described logical file in (embedded)
SQL, always use the physical file/table or a SQL view. The query optimizer
has to rewrite the Query based on the physical file/table before
optimization. At that point it does not know anymore that you specified a
(keyed) logical file. The optimizer decides if an which access path is used.
So if you need your data in a specific order, you have to add an ORDER BY.
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: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Buzz
Fenner via MIDRANGE-L
Sent: Wednesday, 30 April 2025 23:06
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Buzz Fenner <bfenner@xxxxxxxxxxxxxxxx>
Subject: RE: EXTERNAL: SQL Procedure Result Sets Array Processing
On Wed, 30 Apr 2025 14:41:17 Vern Hamberg wrote:
"...creating SQL stored procedures that returns a result set(s) to an
RPG-ILE array..." wasn't clear to me.
Maybe I'm making it too complicated, though! :)
Or, I'm making it too complicated! I'm a complete neophite with stored procs
& UDTF's, so I apologize for probably misusing terminology. Let me start
over by detailing the problem I'm trying to solve. To wit:
1) I'm re-factoring an RPG III program to ILE-RPG.
2) The user is prompted to enter a value in 1 of 3 input fields; the value
is used to fetch 1 or multiple records from a PF.
3) The rub here is that each of the input fields represents a key field;
therefore the program had to manage DB retrieval
using CHAIN & SETLL/READE opcodes in conjunction with three LF's built
over the PF. It's ugly.
4) I wish to externalize as much of the DB access as possible, by having the
SQL code fetch the (multi-column) rows (for
read only) and return them in as few program statements as possible.
I hope this better describes what I'm trying to accomplish. BTW, as a
practical matter I'd wish that the solution is flexible enough to be called
from other environments (i.e. Python, PHP, .NET, etc).
HTH...
--Buzz
--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
