Re: Calling UDTF passing as parameter select field of master file

Hi again,

to answer your question completely - how would you do that, if "getprice" would really be an UDTF?

My solution would be:

select *
from item as i
left join lateral (
select *
from table(getprice(I.CODE))
) as p on true
order by p.price

The column i.code is not a host variable - so no colon (:) is needed. But as you wan the table function to be called for every value of "code" (or better very row of table "item") you can use a LATERAL sub-select in the JOIN clause.

I prefer LEFT JOIN LATERAL ... ON TRUE - but some prefer to use a CROSS JOIN LATERAL at this type of query. The difference would be the behavior if the lateral sub-select doesn't return a row - with a cross join the row from the "leading" table wouldn't show up - with a left join the row would show up and the columns from the sub-select would be NULL.

But as I wrote in the beginning - this all applies only, if "getprice" really is an UDTF.

HTH
Daniel

Am 14.02.2026 um 22:39 schrieb Daniel Gross <daniel@xxxxxxxx>:

Hi Gio,

is "getprice" really an UDTF - an User Defined Table Function? Or is it an UDF - an User Defined Function?

From your description of the function I would say, its a UDF - it receives one parameter (item code) and returns one result (price).

You can use it like this:

select getprice(item.code), item.*
from item
order by 1

or

order by getprice(item.code)

Both will work.

HTH
Daniel

Am 14.02.2026 um 22:26 schrieb gio.cot via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

Hi all

Suppose i have a file master item with two fields : code and descr

i have an UDTF "getprice", that have as input code (master code) and that
return price.

as i would need to have a result sorted for price, i was asking me if it
possible to run this SQL statement

select * from item as i left join table(getprice(:i.code)) as p on i.code =
p.code order by p.price

Thanks in advance
--
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.

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