Re: Referencing columns in temporary tables using SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.