problem with left outer join

I have a sybase query that must be converted to an oracle query but Im getting the error

'a table may be outer joined to at most one other table'

the query used to work in Sybase (i didnt originally write it, I just have to convert it) heres the SQL

Select x, y, z (just for example purpose)
FROM tmp_epg_txs_ids etd,
txs_containers tc,
aspect_ratios ar,
formats f,
ancsvc st,
epg_templates et
WHERE etd.id = tc.id
AND tc.aspect_ratio_id (+)= ar.id
AND tc.format_id (+)= f.id
AND tc.ansv_method_st_code (+)= st.ancsvc_cod
AND tc.epg_template_id (+)= et.id

can anyone help ?

TIA

Hi.

For a left outer join in Oracle, the (+) goes on the right hand side of the "=" to indicate that this item might not be present. Assuming that "etd" and "tc" are present and the other rows may not be you should writ either of these statements in Oracle:

Select x, y, z
FROM tmp_epg_txs_ids etd,
txs_containers tc,
aspect_ratios ar,
formats f,
ancsvc st,
epg_templates et
WHERE etd.id = tc.id
AND tc.aspect_ratio_id = ar.id (+)
AND tc.format_id = f.id (+)
AND tc.ansv_method_st_code = st.ancsvc_cod (+)
AND tc.epg_template_id = et.id (+);

Select x, y, z
FROM tmp_epg_txs_ids etd
JOIN txs_containers tc ON etd.id = tc.id
LEFT OUTER JOIN aspect_ratios ar ON tc.aspect_ratio_id = ar.i
LEFT OUTER JOIN formats f ON tc.format_id = f.id
LEFT OUTER JOIN ancsvc st ON tc.ansv_method_st_code = st.ancsvc_cod
LEFT OUTER JOIN epg_templates et ON tc.epg_template_id = et.id;

Cheers

Tim...

thanks Tim

Im a newbie to oracle, having come from SQL server where left outer join was *= and right outer join was =* (its the same in sybase also), its a bit confusing !

Your solution worked, thank you.

At the risk of confusing misuk11 further, I prefer to write joins the other way round, in the way that my brain considers "forwards". My thinking here (and I appreciate other people seem to be wired up differently) is that you would not generally write

Code:

---

AND 'Smith' = e.ename

---

and SQL syntax does not allow you to write

Code:

---

AND NULL IS e.deptno

---

You instead put the the column to test on the left, and the value or expression you have already established on the right. Thus,

Code:

---

AND e.ename = 'Smith'
AND e.deptno IS NOT NULL

---

and likewise

Code:

---

SELECT x, y, z
FROM  tmp_epg_txs_ids etd
    , txs_containers tc
    , aspect_ratios ar
    , formats f
    , ancsvc st
    , epg_templates et
WHERE  tc.id = etd.id
AND    ar.id (+) = tc.aspect_ratio_id
AND    f.id (+) = tc.format_id
AND    st.ancsvc_cod (+) = tc.ansv_method_st_code
AND    et.id (+) = tc.epg_template_id;

SELECT x, y, z
FROM  tmp_epg_txs_ids etd
      JOIN txs_containers tc ON tc.id = etd.id
      LEFT OUTER JOIN aspect_ratios ar ON ar.i = tc.aspect_ratio_id
      LEFT OUTER JOIN formats f ON f.id = tc.format_id
      LEFT OUTER JOIN ancsvc st ON st.ancsvc_cod = tc.ansv_method_st_code
      LEFT OUTER JOIN epg_templates et ON et.id = tc.epg_template_id;

---

Whichever way around you write it, all SQL cares about is that the (+) operator is immediately to the right of the key from the outer joined table.