AW: Referencing columns in temporary tables using SQL

What about:

with t1 as ( select rpan8, count(*) as MyCount
from f0311
group by rpan8
order by rpan8
)
select * from t1
Where myCount > 1000

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jonathan Mason
Gesendet: Tuesday, 18. August 2009 15:51
An: midrange-l@xxxxxxxxxxxx
Betreff: Referencing columns in temporary tables using SQL

I am trying to run an SQL statement that creates a temporary table over
which I want to run some selection criteria:

with t1 as ( select rpan8, count(*)
from f0311
group by rpan8
order by rpan8
)
select * from t1

Ideally I would like to add a "WHERE" clause to the select to only show
entries where the COUNT(*) is more than a given amount, say 10000.
However, I can't find any way to reference the generated column as I
don't know the internal name assigned to the column.

I have tried using "where count(*) > 10000" and other variations, but
all to no avail. Does anybody have any ideas?

Thanks

Jonathan


Jonathan Mason
iSeries Consultant
www.astradyne-uk.com


_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________