Lookup

rasi · Post by **rasi** » Fri Oct 25, 2002 1:35 am

hi,

I need to know which is the best way to use the lookup in DS. All I want is to pass a value to a Oracle table and get back the lookup value.

can anyone help me please.

thanks
rasi

Edited by - rasi on 10/25/2002 02:42:22

ray.wurlod · Post by **ray.wurlod** » Fri Oct 25, 2002 5:46 pm

There are essentially three ways:
OCI stage supplying a reference input link
ODBC stage supplying a reference input link
Hashed File supplying a reference input link

If Oracle is on a separate machine from DataStage, the third of these is by far the preferred option, as reference lookups (one row at a time) makes inefficient use of the network.
You pre-load the hashed file from the Oracle table, selecting only the columns that you need for the search. This typically does not take very long.
Similarly, using an OCI or ODBC stage, you refer only to the search and return columns (that is, you don't process any data you don't have to).

rasi · Post by **rasi** » Sun Oct 27, 2002 10:37 pm

Hi

Thanks for your answer. In continuation to your reply. How could i use outer join in the oci reference input link.

many thanks
rasi

There are essentially three ways:
OCI stage supplying a reference input link
ODBC stage supplying a reference input link
Hashed File supplying a reference input link

If Oracle is on a separate machine from DataStage, the third of these is by far the preferred option, as reference lookups (one row at a time) makes inefficient use of the network.
You pre-load the hashed file from the Oracle table, selecting only the columns that you need for the search. This typically does not take very long.
Similarly, using an OCI or ODBC stage, you refer only to the search and return columns (that is, you don't process any data you don't have to).

ray.wurlod · Post by **ray.wurlod** » Sun Oct 27, 2002 11:16 pm

You can specify user-defined SQL and create your own SELECT statement to implement an outer join. For each "key value" for which you provide a key expression in the Transformer stage you need to provide a parameter (for example :1, :2 and so on) in your SQL statement. You also need to include some mechanism to guarantee the return or only only one row (the ODBC stage has a mechanism for returning more than one row, but the OCI stage does not).