| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Strange results
Read much...I already posted the correct query. Just drop it. Charles
answered my question and helped me out. What have you done for the
group..except cut people down and look like a group troll that provides
no useful information.
And please do not speak for someone else. And if I had wanted your advice..I would have replied directly to you....which I did not until now.....bad mistake on my part.
Tom
DA Morgan wrote:
> tawright915 wrote:
> > unfortunatly I still do. In theory I agree with you, however this is
> > not the case when I run this query.
> >
> > Tom
> > Serge Rielau wrote:
> >> tawright915 wrote:
> >>> Thank you so much.....I see what you are talking about.
> >>>
> >>> Once again you have been very helpful.
> >>>
> >>> Thanks
> >>> Tom
> >>>
> >>> Charles Hooper wrote:
> >>>> tawright915 wrote:
> >>>>> Charles,
> >>>>> I do not see it. If I strip out my qualifiers, I'm joining building to
> >>>>> premise then bill_acct to premise.
> >>>>> Isn't a Cartesian join where you join one table to another where there
> >>>>> is no key the same between them?
> >>>>>
> >>>>> Thanks
> >>>>> Tom
> >>>>>
> >>>>> Oh and for the correct code it's
> >>>>> Select distinct a.AD_SERV_CITY,
> >>>>> a.cd_city_cnty,
> >>>>> d.ky_ba,
> >>>>> d.ky_prem_no
> >>>>> from building a,
> >>>>> premise c,
> >>>>> bill_acct d
> >>>>> where
> >>>>> ((a.cd_city_cnty in ('0627')
> >>>>> and a.KY_BLDG_NO = c.KY_BLDG_NO
> >>>>> and c.ky_prem_no = d.ky_prem_no
> >>>>> and d.cd_ba_stat = '02')
> >>>>> or (upper(a.AD_SERV_CITY) like '%BYRNES
> >>>>> MILL%'))
> >>>>> order by 1, 3, 4 ;
> >>>> Cartesian join?
> >>>> SELECT DISTINCT
> >>>> A.AD_SERV_CITY,
> >>>> A.CD_CITY_CNTY,
> >>>> D.KY_BA,
> >>>> D.KY_PREM_NO,
> >>>> FROM
> >>>> BUILDING A,
> >>>> PREMISE C,
> >>>> BILL_ACCT D,
> >>>> WHERE
> >>>> (
> >>>> (
> >>>> A.CD_CITY_CNTY IN ('0627')
> >>>> AND A.KY_BLDG_NO = C.KY_BLDG_NO
> >>>> AND C.KY_PREM_NO = D.KY_PREM_NO
> >>>> AND D.CD_BA_STAT = '02')
> >>>> OR (
> >>>> UPPER(A.AD_SERV_CITY) LIKE '%BYRNES MILL%')
> >>>> )
> >>>> ORDER BY
> >>>> 1,
> >>>> 3,
> >>>> 4;
> >>>>
> >>>> Look closely at the ( ) and the placement of the OR. The SQL statement
> >>>> as written requires that if UPPER(A.AD_SERV_CITY) LIKE '%BYRNES MILL%'
> >>>> then it is OK to have a Cartesian join between the three tables. If
> >>>> UPPER(A.AD_SERV_CITY) is not LIKE '%BYRNES MILL%', then the three
> >>>> tables are being joined together properly. You may need to relocate
> >>>> the ( ) and the OR - this is not easy to see with the way your SQL
> >>>> statement was formatted.
> >> Are you getting duplicate records without or with DISTINCT?
> >> The cartesian product explaine dups if you ommit DISTINCT.
> >> But the query as posted should not return any dups no matter what is in
> >> the FROM or WHERE clauses the way I see it.
> >>
> >> Cheers
> >> Serge
> >> --
> >> Serge Rielau
> >> DB2 Solutions Development
> >> IBM Toronto Lab
> >>
> >> WAIUG Conference
> >> http://www.iiug.org/waiug/present/Forum2006/Forum2006.html
>
>
>
 |
 |