Best way to convert to jobs

[friend.kak@gmail.com]

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

[vasa_dxx]

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.

[chulett]

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.

[kamalshil]

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