Re: Referencing columns in temporary tables using SQL

["Jonathan Mason " <jonathan.mason@xxxxxxxxxxxxxxxx>]

Many thanks to everyone who responded to my question, it really is
appreciated.

I totally missed the HAVING clause in the SQL book I was looking at, and
the AS clause wasn't listed.  Time to get a more up to date book I
guess.

I tried both methods and noticed that over a file with 4,000,000+
records the HAVING returned the first page of results in approximately
24 seconds whilst the AS only took around 2 seconds.

All the best and thanks again

Jonathan



Rob@xxxxxxxxx wrote :

> Birgitta's works also.
>
> Get in the habit of using AS, especially in summations.
> Like
> select count(*) as TheCount...
> This will rename your fields (excuse me, columns).
>
> Might want to try it in a view that the users use to make the
> more readable.
> selection For example, instead of telling the users to join this file
> this and that for a Query/400 query then just create this
>
> CREATE VIEW QTEMP/OPENORDERS AS (
> SELECT HCUST AS CUSTNBR,
>        HORD  AS ORDERNBR,
>        HCPO  AS CUSTPONBR,
>        LLINE AS ORDERLINE,
>        LPROD AS ITEMNBR,
>        LCLAS AS ITEMCLASS,
>        LQORD - LQSHP AS QTYLEFT,
>        NUMTODATE(LRDTE) AS DATEREQSTD,
>        CASE LSTAT
>         WHEN 'E' THEN 'DESC1'
>         WHEN 'I' THEN 'DESC2'
>         WHEN 'P' THEN 'DESC3'
>         ELSE 'DESC4'
>        END AS LINESTATUS,
>        LDESC AS ORDLINDESC,
>        CNME  AS CUSTNAME,
>        CSAL  AS SLSMANNBR
> FROM GDIDIVF/ECH
>  LEFT OUTER JOIN GDIDIVF/ECL ON HORD=LORD
>  LEFT OUTER JOIN GDIDIVF/RCM ON HCUST=CCUST
> )
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:
> rob@xxxxxxxxx
> To:
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Date:
> 08/18/2009 10:26 AM
> Subject:
> Re: Referencing columns in temporary tables using SQL
> Sent by:
> midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> 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
>
>
>
> to this and I am trying to run an SQL statement that creates a
> 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
>
> temporary table over Ideally I would like to add a "WHERE" clause to
> 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.
>
>
>
> --
> the select to only show This is the Midrange Systems Technical
> 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.


Jonathan Mason
iSeries Consultant
www.astradyne-uk.com


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