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 -> Re: Problem with outer join

Re: Problem with outer join

From: Jacques Desmazieres <jacques.desmazieres_at_is2france.com>
Date: 2000/06/30
Message-ID: <WJX65.4046$mb4.7519353@nnrp4.proxad.net>#1/1

Hi,

This is due to the fact that when a.cus1bo is null, you do not have a row, so a.addrno is null, so join on a.addrno = ca.addrno fails, so query fails.

Jacques
"Kram" <kram_at_biosys.net> wrote in message news:395BD639.C01CE35C_at_biosys.net...
> 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.mul conflag,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.custyp
e,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(cngcoun t,':'),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 Fri Jun 30 2000 - 00:00:00 CDT

Original text of this message

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