User-defined SQL for LookUp

arindami · Post by **arindami** » Thu Jul 28, 2005 9:02 am

Hi
Is there anyway to have a user-defined SQL in DataStage without wirting
the select inside a batch i.e. only by using some stage in a sever job.
For instance I want to do a look up based on on a complex SQL involving
joining 3/4 tables , outer join on one and having a decode clause . etc.

Thanks

pnchowdary · Post by **pnchowdary** » Thu Jul 28, 2005 9:40 am

Hi,

You can achieve this using the appropriate stage (ODBC,ORACLE etc) depending upon on the database on which your tables reside. In these stages, you have a place where you can put in your User Defined SQL.

kduke · Post by **kduke** » Thu Jul 28, 2005 11:00 am

I would take your select and create a temp table with it. Import the metadata. Change the metadata so the fields that need prompts for are keys. Build your ODBC or OCI lookup then switch it to user defined. This will save lots of time.

Do you know how to CREATE TABLE using a SELECT statement?

ririr · Post by **ririr** » Thu Jul 28, 2005 12:47 pm

SELECT A.COL1, B.COL1, A.COL2, B.COL2, C.COL1 from TAB1 A, TAB2 B, TAB3 C WHERE A.KEY = B.KEY(+) AND B.KEY=C.KEY or can use a sub-query in the user defined sql in DRS stage, if you are using DS7 and higher.

The A.COL1, B.COL1, A.COL2, B.COL2, C.COL1 will be the columns in the DRS/Oracle/ODBC stage...

Hope this helps..

ray.wurlod · Post by **ray.wurlod** » Thu Jul 28, 2005 9:20 pm

You can perform ANY legal SQL from a suitable passive stage type. The only extra thing you have to do is to make sure that your Columns grid contains the correct number of key and non-key columns so as to match what's specified (or implied) in the SQL. For example, parameter markers in WHERE clauses are matched by Key columns.