Re: SQL CORR SUBQUERY EXPERTS

In article <4tasrc$r8k_at_newsbf02.news.aol.com>, mgreen1962_at_aol.com (MGreen1962) writes:

> Hello, 2 all that would try to answer this question.
>
> How would I write a query that returns group rows where the count >= some
> value.
>
> i.e.
> select table_name,count(*)
> from sys.dba_constraints
> where count(*) >= 7
> group by table_name;
>
> I know the count(*) function cannot be used in a where clause. I tried to
> use a correlated subquery and my system locked each time. My query is as
> follows:
>
> select table_name,count(*) from sys.dba_constraints x
> where to_number('1')>=(select count(*) from sys.dba_constraints where
> x.table_name=table_name)
> group by table_name;

i think you want something like this:

select table_name, constraints
  from (select table_name, count(*) constraints

          from sys.dba_constraints 
         group by table_name)

be careful though, since this query may not be returning exactly what you want in the way of info. for instance, more than one user may own a table with the same name. this query will merge all constraints for all owners of a particular table into its returned rows...

HTH                   david