Re: stored procedure(?) question

Depends on the logic required to generate the list...

If it can be moved to SQL with a reasonable amount of effort, then by all
means that's the better option.

But if not, then encapsulating the RPG logic in a UDTF is a great way to
easily reuse it.

As the title of Scott's article says, "UDTFs in RPG for the Win"


Charles

On Thu, Apr 4, 2019 at 8:21 AM Mark Waterbury <
mark.s.waterbury@xxxxxxxxxxxxx> wrote:

Tim,
If the list is "generated from files" why not just create an SQL View to
encapsulate the SQL needed to generate the desired view directly?


This gives the SQL Query Engine Optimizer a chance to determine the best
way to access the required data, vs. calling an RPG program where the
"navigation" is "hard-coded" e.g., using "native" I/O, (like SETLL/READ
etc.).

HTH,
Mark S. Waterbury



On Thursday, April 4, 2019, 9:43:33 AM EDT, tim <
iseriesstuff@xxxxxxxxx> wrote:

Hi Vern,

the list is generated from files on the iseries. I am using an RPG
program to create the "list". Its basically 1 field that contains a list
of invoice number. So after my rpg program is done, the list might look
like:

INV1
INV2
INV5
INV15

i would like to take the results of this and us them in an sql like:

select claims from claimtable where invoicenbr in (inv1,inv2,inv5,in15).

Does that make any sense?

On 4/4/2019 9:46 AM, Vernon Hamberg wrote:

Hi Tim

The list has to be in a data file of some kind - if it's in QTEMP or
wherever, you can use it.

What kind of thing is the list? It could be an array, content in a
user space, whatever. Let us know, we can help better, then.

Vern

On 4/4/2019 7:50 AM, tim wrote:

I have an rpg program that generates a list of invoices (not using
sql to create list). I would like to use this generated list in sql.

so,

select * from tablelist where invno in (rpg program generated list)

is this possible?

--
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@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
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@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com