Re: SQL UDF to return one column from a row

[Roger Harman <roger.harman@xxxxxxxxxxx>]

This is a generic lookup file.  There are almost 6000 different BusinessUnit/KeyType pairs and those encompass almost 250,000 records with different values.

So, I need a flexible solution.  If it was just that one example then, yes, a view would be fine.   

Instead of the example I gave, maybe this makes more sense:  GetValueFromLookupFile(BusinessUnit, 'KeyType', KeyValue)

examples:
BusinessUnit is the current company being processed - we have about 3 dozen business units
'KeyType' may be 'BRND', 'VNDR',  'STAT'......  (needs to be a literal)
KeyValue might be 'JACK', ' HVNH', 'CA'   (this would be a value from another table)

For the life of me, I don't know why I was having so much trouble.  Went back to it yesterday and it worked on the first try.  The only difference was using STRSQL the second time and was using SQL Workbench/J the first.

This is going to be such a time saver.  I may need this logic 5,6, or more times in a single query.  Beats the heck out of a bunch of joins.

Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power
 



From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Robert Rogerson <rrogerson@xxxxxxxxxxx>
Sent: Thursday, March 30, 2017 6:07 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL UDF to return one column from a row
    
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