Sorry but its something like this

with wftbl(

select col1,..col5 from objectA

inner join objectB

on cond1 =cond1

where ...

)

....

tbl2(

object c

inner join

objectA

where . ..

)

select col1 .. .

from

maintabl

....

left join (select * from objectA where condtn)worktbl

...

left join objectA

on (worktbl.id = objectA.ID AnD worktbl.order >1000)

OR

(worktbl.anotherid = objectA.ID AnD worktbl.order =1000)

left join

....