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 <[email protected]>:
> 
> 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 <[email protected]>:
>> 
>> 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: [email protected]
>> To subscribe, unsubscribe, or change list options,
>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>> or email: [email protected]
>> Before posting, please take a moment to review the archives
>> at https://archive.midrange.com/rpg400-l.
>> 
>> Please contact [email protected] for any subscription related questions.
>> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.