DSXchange

Hi,

I am having a 300 lines of SQL(Oracle), which is joining 36 tables altogether in the same SQL.

Ex:

Select c1.a, c2.b
from c1,c2
where c1.a in
( select b1.a
from b1,b2
where b1.a in
(
select a1.a
from a1,a2
where a1.a in
(
.......
)
)
)

, like that , It is something like inline views in oracle kinda. can anyone let me know what is the bestway to implement the similar logic using Datastage.

>
I just want to simulate the same in Datastage instead of pasting big SQL in one Oracle query
Please throw your views on this.
Thanks.
friend.kak@gmail.com

We also had the similar scenario in our project. We observed that using user defined SQL is efficient instead of using multiple stages in a job.
we had a SQL having 17 joins & all joins were used in a single OE Stage. it works.
But if you need to apply some logic after the data sourcing/Joining, please elaborate the same.

Stick with the 'big sql' that works rather than breaking it all up to be more 'DataStage like'. If it turns out to be 'too long' for the stage, create a view in your DB and source from the view.

Firing SQL is best way.
Multiple join and then sorting of data and partioting may take more time.