Re: Cool but ugly SQL -- MIDRANGE-L

LOL Dan - you've definitely come over to the dark side!

I see what you are saying, and, yes, only the columns in the GROUP BYor aggregate functions can be used in the select list. But in thecase of JOINs, if the join columns are also the grouping columns, itis a toss-up which you should use - and that seems to be the casehere. The nice thing (sometimes messy, I suppose) about SQL is thatyou can group and order across file boundaries, not like join logicals.

As to an example of nested table expression that is equivalent to thecommon one (using WITH) that is sort of what you are doing andleaving out the Insert,


with xx as (SELECT shh_lib,
                              shh_file,
                              shh_member,

max(char(shh_lcdate) ||char(shh_lctime)) shh_dttm

                frOM srcmbrhash
                GROUP BY shh_lib, shh_file,shh_member)
select hh.* from xx
                  join srcmbrhash hh
                     on xx.shh_lib=hh.shh_lib and
                         xx.shh_file=hh.shh_file and
                         xx.shh_member=hh.shh_member and
                         char(hh.shh_lcdate) || char(hh.shh_lctime) = shh_dttm

could be

select hh.* from (SELECT shh_lib,
                              shh_file,
                              shh_member,

max(char(shh_lcdate) ||char(shh_lctime)) shh_dttm

                frOM srcmbrhash
                GROUP BY shh_lib, shh_file,shh_member) as xx
                  join srcmbrhash hh
                     on xx.shh_lib=hh.shh_lib and
                         xx.shh_file=hh.shh_file and
                         xx.shh_member=hh.shh_member and
                         char(hh.shh_lcdate) || char(hh.shh_lctime) = shh_dttm

where the table expression is found where you would normally have a table name.

In either case, they are like temporary views, which are basicallySELECT statements stored in an LF that are executed at runtime.


I recommend the SQL Reference on IBM's site for more on this.

HTH
Vern

At 12:35 AM 6/18/2006, you wrote:

On 6/16/06, vhamberg@xxxxxxxxxxx <vhamberg@xxxxxxxxxxx> wrote:
>
> Cool - Birgitta's post was important. I've a question, though. You say you
> did not want Tempfile to be the source of the data. Can you say more - I
> mean, as far as I know, it is irrelevant - TempFile is just a name for the
> result of the common table expression defined by the "with" - the data
> ultimately comes from the original file, too. Anyway, I am just curious
> about that statement.


I guess I'm not an SQL newbie anymore, but still a novice.  In my limited
and few & far between, my aging (perhaps incorrect) recollection was that
GROUP BY limits the columns that can be selected to those in the GROUP BY
clause and I wanted to include in my output table all of the fields from the
original file

BTW, that select statement for the max values could probably also have been
> written in place in the join, as what is called a nested table expression.
> Not always, but usually - YMMV.


Got a code example to get me started?  ;-)

Thanks,
Dan
--
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.