× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.