Name the Count field, then you can reference it. like;

with t1 as ( select rpan8, count(*) as Ttl_Number
from f0311
group by rpan8
order by rpan8
select * from t1
Where Ttl_Number > 5



On Tue, Aug 18, 2009 at 7:50 AM, Jonathan Mason <
jonathan.mason@xxxxxxxxxxxxxxxx> wrote:

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?



Jonathan Mason
iSeries Consultant

This message was sent using NOCC v1.14 webmail software

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].