Re: SQL UDF to return one column from a row

Looks something like this....

Company
KeyType
KeyValue
Text
NumericValue1
....
NumericValueN
CharValue1
....
CharValueN

A few different columns of different length, scale/precision. Text is used the most but other columns are also used.
example:
Company '01'
KeyType 'CMGR'
Text 'Joe Smith'
CharValue6 <employee number>

There are a lot of pro's and con's to this approach but the table I use is an integral part of our ERP.

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

 
 





From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Nathan Andelin <nandelin@xxxxxxxxx>
Sent: Thursday, March 30, 2017 8:53 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL UDF to return one column from a row
 

Also, the external file is multi-use and contains descriptions and other
data for thousands of circumstances.

Could you be more specific about the layout of that file?

example:  select CompanyNbr, ItemNbr, SupplierNbr,
GetSupplierName(CompanyNbr, ItemNbr, SupplierNbr) from ItemMaster

Given a function named GetSupplierName(), what is the purpose of passing
CompanyNbr and ItemNbr as opposed to just passing SupplierNbr?

It's not clear why an SQL join wouldn't work. I assume there's more to this
than meets the eye.

Your question caught my eye because I'm just now working a utility function
that returns any column from any file, given FileName, ColumnName, and
KeyValue.

rlaColGet(FileName, ColumnName, KeyValue);