Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Problem with outer join
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
![]() |
![]() |