Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Problem with outer join

Problem with outer join

From: Kram <kram_at_biosys.net>
Date: 2000/06/29
Message-ID: <395BD639.C01CE35C@biosys.net>#1/1

I have a problem with an outer join (doesn't seem to work). Can anybody help me with this piece of code? The Cus1no field is sometimes NULL.

SELECT
a.adno,a.vno,a.runno,a.cus1name,a.cus2no,a.cus2name,a.cus2tel,a.addrno ,a.caller,a.adrname,s.salno,a.cus4tel,a.startdate,a.enddate,a.cus4name,a.mulconflag,a.ad_type,a.pubcnt,a.boxno,a.boxtype,a.ratecode,a.agreement,a.unet,a.rdate,a.checkads1,a.checkads2,a.checkads3,a.remarks,a.changereason,c.custype,c.cgrno,c.acusno,ca.address1,ca.address2,ca.address3,ca.state, substr(ca.postno,1,5) || decode(ca.postno,null,null,'') || substr(ca.postno,7,4),
cn.string1,ltrim(ltrim(a.frequency,'U'),'S'),a.vnoflag,a.salno,ltrim(cngcount,':'),a.transcode, c.acusno
FROM ad a, pub p, customer c, cusaddress ca,codename cn, salesperson s WHERE a.cus2no=c.cusno and c.cusno=ca.cusno and a.cus2no=ca.cusno and upper(a.receiver)=upper(s.username(+)) and a.addrno=ca.addrno and a.adno=p.adno and a.vno=p.vno and p.pubno=1 and cn.name=p.state
--this is the problem join

and c.cusno=a.cus1no(+)

and ((a.runno=183) and to_date(a.enddate) >= to_date(sysdate) or (p.runno=183 and p.state='VAR')
or (p.runno2=183 and p.state in ('PER','VAR'))) Received on Thu Jun 29 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US