DSXchange

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

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.

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?

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..

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.