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
User-defined SQL for LookUp
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA
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?
Do you know how to CREATE TABLE using a SELECT statement?
Mamu Kim
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..
The A.COL1, B.COL1, A.COL2, B.COL2, C.COL1 will be the columns in the DRS/Oracle/ODBC stage...
Hope this helps..
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.