Re: Referencing columns in temporary tables using SQL

Just to expand on the suggestions of using HAVING

Unlike a WHERE clause which is checked for each row read, the HAVING
check occurs for each row in the result set after the aggregation.

So for instance this statement
with t1 as ( select rpan8, count(*) as theCount
from f0311
group by rpan8
order by rpan8
)
select * from t1
where theCount > 10000

is the same as

select rpan8, count(*) as theCount
from f0311
group by rpan8
having count(*) > 10000
order by rpan8

HTH,
Charles

On Tue, Aug 18, 2009 at 10:20 AM, <rob@xxxxxxxxx> wrote:

HAVING is your answer...

  with t1 as (  select rpan8,
                       count(*) as TheCount
                  from f0311
              group by rpan8
              having count(*)>10000
              order by rpan8
             )
  select * from t1


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
"Jonathan Mason " <jonathan.mason@xxxxxxxxxxxxxxxx>
To:
<midrange-l@xxxxxxxxxxxx>
Date:
08/18/2009 09:55 AM
Subject:
Referencing columns in temporary tables using SQL
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



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
_______________________________________________________




--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.