Re: SQL statement question. please help ...

Here's a statement that works. There surely a way to enhance it but i'm afraid i have no time to do this.

select distinct decode(sign(a.nb-500),1,'TOO MANY', t_a.aaa) from t_a, (select count(distinct aaa) nb from t_a) a;

Note that if column aaa is a number or a date, Oracle converts it to a string because of the string 'TOO MANY' in the decode.

Jimmy a écrit dans le message <37DF247A.2C5DEC07_at_comp.polyu.edu.hk>...
>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 - 09:51:49 CDT