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
