Function to do This?

What is the easiest way to retrieve the value of columns from "Lookup" tables (a table whose primary key is another tables foreign key)? I would like to create a function which would accept three parameters:

• A Table name (i.e. the lookup table - has a primary key which can span more than one column)
• Search String
• Return Column Name (the name of the column that you want the value of for the searched string)

The function will do a search for Search String on the Primary Key of the Table. I know this is just a basic "JOIN" using primary and foreign keys. But is there a way that I could write a simple function to do this? Is this something that Oracle can do automatically? It has all of the necessary information.

Some examples of calls:
LookUp('JOBS','VP','JOBDESC'); --Returns "Vice President" LookUp('CITIES','CA'+'SF','ZIPCODE') --Returns ZipCode for S.F. CA (Note, CITIES primary key is "STATECODE+CITYCODE");

Thanks, Stan. Received on Tue Apr 06 1999 - 09:18:05 CDT