Outer join with mutiple tables

[spicysudhi]

Table1: customer
custid cust_chnl
1 A

Table2: Channel
chnlid CustChnl warehouse def_qty
AAA A AA 100

Table3: Order
ordno custid warehouse qty
1 1 AA 10
2 1 AC 15

Output required:
ordno custid def_qty
1 1 100
2 1 50

Here Channel table may hav a record combination of custChnl and warehouse or may not. If exists i need to take the def_qty from there else have a constant value of 50.

the problem i am facing is I am not able put the outer join for Channel table from both Order and Customer.

select
o.ordno, o.custid, nvl(ch.def_qty,50)
from
order o, customer c, channel ch
where
o.custid= c.custid and
o.warehouse=ch.warehouse (+) and
ch.cust_chnl = ch.cust_chnl (+);

please help.
regards,
sudhi

 

[randall2nd]

Try this, it is a WAG but seems to work on a limmited basis, but I have no Idea how any of your data is being used.

I cannot think of a good way to do this.

select o.ordno, o.custid, nvl(ch.def_qty,50)
  from orders o, customer c, channel ch
 where o.custid = c.custid
   and o.warehouse = ch.warehouse (+)
   and c.cust_chnl = nvl(ch.custchnl,c.cust_chnl)
 order by o.ordno;

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.