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
Please don't top post.
Again ... I know for a fact that what you posted as your SQL
statement has never run in Oracle. What is the actual statement?
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
is two commas away from being valid.
With all due respect to Charles Hooper: Until we see the actual
statement ... any advice is premature.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Nov 01 2006 - 13:38:42 CST
