Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Function to do This?

Re: Function to do This?

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Thu, 08 Apr 1999 10:20:32 +0900
Message-ID: <370C1270.5210@bhp.com.au>


DelphiDev wrote:
>
> 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.

For a "finite" list of possibilities, you can use package overloading as another poster has indicated...

If you want a truly generic pl/sql routine, you would need to use dbms_sql to prepare your sql string dyanmically...

HTH
--



Connor McDonald
BHP Information Technology
Perth, Western Australia
"Never wrestle a pig - you both get dirty and the pig likes it..." Received on Wed Apr 07 1999 - 20:20:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US