Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL statement question. please help ...

Re: SQL statement question. please help ...

From: Suresh <sureshkt_at_hotmail.com>
Date: Tue, 14 Sep 1999 18:09:35 GMT
Message-ID: <37DE8F5F.4EA0F9C8@hotmail.com>


Hi Try this one

	select decode(sign(count(distinct(AAA))-500),1,'TOO MANY',0,
		count(distinct(AAA)) Result from T_A;
Wishes
Suresh

Jimmy wrote:
>
> Hello all,
>
> I want to do a query by following requirements:
>
> I want to retrieve the distinct value of AAA of table T_A if the
> count(distinct(AAA)) of this SQL statmenet is smaller than 500,
> otherwise returns 'TOO MANY';
>
> i.e. select distinct(AAA) from T_A if count(distinct(AAA)) <= 500;
> select 'TOO MANY' from T_A if count(distinct(AAA)) > 500;
>
> I don't know how to write a SQL statement to do the above
> requirements. Is is possible to do this by one SQL statement? (and this
> SQL statment has the shortest response time and AAA may or may not the
> index of the table T_A)
>
> Does Oracle needs two table scan in using one SQL statement to do
> the above query? First, scan the table T_A and get the
> count(distinct(AAA)). If <=500, then scan the table again and retrieve
> the distinct(AAA), am I right?
>
> Thanks,
> Jimmy
Received on Tue Sep 14 1999 - 13:09:35 CDT

Original text of this message

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