Re: SQL UDF to return one column from a row

[Robert Rogerson <rrogerson@xxxxxxxxxxx>]

Roger, couldn't you just use a view

CREATE OR REPLACE VIEW Item_Supplier AS

SELECT i.CompanyNbr, i.ItemNbr, i.SupplierNbr, s.SupplierName
  FROM ItemMaster i
  LEFT OUTER JOIN SupplierMaster s on  i.SupplierNbr = s.SupplierNbr);

Then just

SELECT *
  FROM Item_Supplier;

Do you really need a function?

Rob


On 3/29/2017 6:00 PM, Roger Harman wrote:
> I've been going around and around on this.  I've done scalar UDF's but this has me stumped.
>
> I want to create a UDF that returns a text description from a reference file.  In this case, supplier names are externally stored.
>
> I currently do this with a join but it is a pain to repeat over & over.   Also, the external file is multi-use and contains descriptions and other data for thousands of circumstances.  So, this could be a big time saver for me.
>
> example:  select CompanyNbr, ItemNbr, SupplierNbr, GetSupplierName(CompanyNbr, ItemNbr, SupplierNbr) from ItemMaster
>
> I have yet to find a decent example showing this.  Does anyone have an example?  Also having trouble with long name versus specific name.
>
> Thanks!
>
> Roger Harman
> COMMON Certified Application Developer - ILE RPG on IBM i on Power