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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
MIDRANGE-L Info Page<
http://lists.midrange.com/mailman/listinfo/midrange-l>
lists.midrange.com
To unsubscribe from MIDRANGE-L, get a password reminder, or change your subscription options enter your subscription email address:
MIDRANGE-L Info Page
lists.midrange.com
To unsubscribe from MIDRANGE-L, get a password reminder, or change your subscription options enter your subscription email address:
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
midrange.com -- MIDRANGE-L mailing list archive<
http://archive.midrange.com/midrange-l>
archive.midrange.com
midrange.com MIDRANGE-L mailing list archive
midrange.com -- MIDRANGE-L mailing list archive
archive.midrange.com
midrange.com MIDRANGE-L mailing list archive
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
