Re: correlated query as a column and where clause

[salah jubeh <s_jubeh@xxxxxxxxx>]

Hello Harald,

Danke ! .  My concern is why I get error undefiend attribute if I used the alias in the where clause

Regards

 

---

From: Harald Armin Massa <harald@xxxxxxxxxxxxxxx>
To: salah jubeh <s_jubeh@xxxxxxxxx>
Cc: pgsql <pgsql-general@xxxxxxxxxxxxxx>
Sent: Fri, April 15, 2011 5:26:45 PM
Subject: Re: correlated query as a column and where clause

I am wondering,  why I can not add the following  '  A > 10'  in the where  clause  i.e.   'where nspname !~* 'pg_'  and A > 10'

Select nspname, (SELECT count(*) as count FROM pg_tables where schemaname = nspname) as A
FROM pg_namespace
where nspname !~* 'pg_'

what you are looking for is the having clause

Select nspname, count(1) 

 FROM pg_tables 

where nspname !~* 'pg_' 

group by nspname

having count(1)  >10

best wishes

Harald

--
Harald Armin Massa     www.2ndQuadrant.com
PostgreSQL  Training, Services  and Support

2ndQuadrant Deutschland GmbH    
GF: Harald Armin Massa
Amtsgericht Stuttgart, HRB 736399