Outer join in classic Oracle join syntax

Question

Hello,

I need to convert my query to classic Oracle join syntax because I need to put it in a materialized view. However, I'm not quite sure how to do it:

SELECT * FROM transactions t
LEFT JOIN transac_detail td1 ON (t.id = t1.trans_id AND t.ttype = 'VOICE')
LEFT JOIN transac_detail td2 ON (t.id = t2.trans_id AND t.ttype = 'BROADBAND');

I started writing it like this:

SELECT * FROM transactions t, transac_detail td1, transac_detail td2
WHERE t.id = t1.trans_id(+) AND t.id = t2.trans_id(+)

But how can I include the "literal" conditions?

Answer 1

The original query confused. do you mean this?

SELECT t.* FROM transactions t, transac_detail td1
WHERE t.id = td1.trans_id(+)
and t.ttype = 'VOICE'
union
SELECT t.* FROM transactions t, transac_detail td2
WHERE t.id = td2.trans_id(+)
and t.ttype = 'BROADBAND'