Re: Having without group by clause

From: Thomas Sommerfeld <Thomas.Sommerfeld_at_Baden-Online.de>
Date: 1997/11/27
Message-ID: <347DF72F.59B24C62@Baden-Online.de>#1/1

Dies ist eine mehrteilige Nachricht im MIME-Format.

--------------7561CCFAE44A110F0D917A92
Content-Type: text/plain; charset=us-ascii
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Transfer-Encoding: 7bit

Hi,

I think the optimizer recognize the EXISTS in query 1 and so Oracle tries to find any value that satifies the having condition in the subquery. In query 2 you want to get the list of column III and Oracle tries to group the values.
If you try the following statement

Select I,II from X

     where X.I in
     ( select III from Y
       where X.I=Y.III
       having sum(IV) > 1 ) ;

Oracle won't work, because it tries to get a list of column Y.III

Bye

Tom

krishnanand_at_hotmail.com schrieb:

> Hi,
>
> Consider 2 tables: X & Y with 2 columns each as shown below:
>
> X Y
> ----------- -----------------
> I | II III | IV
> ----|------ --------|--------
> 10 | A 10 | 1
> 20 | B 10 | 2
> ----------- ------------------
>
> If I give the Query as:
>
> (1) Select I,II from X
> where exists
> ( select III from Y
> where X.I=Y.III
> having sum(IV) > 1 ) ;
>
> The Query works fine . One thing to NOTICE here is that, in the
> subquery the column being selected & the column being aggregated in
> the
> having clause are different !!!
>
> I believe in the subquery , for X.I=10 it will fetch 2 rows ( (10,1)
>
> (10,2) ) treat them as a group ( taking III as the grouping column ) &
>
> sum on the IV column. To test this
>
> I did something else, as follows :
>
> (2) I executed the queries :
>
> (a) select III from Y having sum(IV) > 1 ;
>
> (b) select III from Y where 1>2 having sum(IV) > 1 ; --- thinking
> that
>
> WHERE clause may be necessary. BUT BOTH FAIL.
>
> My question is : How is the subquery situation in (1) DIFFERENT
> from
> the situation in (2).
>
> Please reply by EMail also.
>
> Thanx.
>
> -------------------==== Posted via Deja News
> ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

--------------7561CCFAE44A110F0D917A92
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Visitenkarte für Thomas Sommerfeld
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Thomas Sommerfeld
n:              Sommerfeld;Thomas
org:            Softwareentwicklung Sommerfeld
adr:            Im Stenglenz 12a;;;Berghaupten;;77791;Deutschland
email;internet: Thomas.Sommerfeld_at_Baden-Online.de
tel;work:       +49 7803 980914
tel;fax:        +49 7803 980916

x-mozilla-cpt: ;0
x-mozilla-html: FALSE
end: vcard

--------------7561CCFAE44A110F0D917A92-- Received on Thu Nov 27 1997 - 00:00:00 CST