× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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 BY or aggregate functions can be used in the select list. But in the case of JOINs, if the join columns are also the grouping columns, it is a toss-up which you should use - and that seems to be the case here. The nice thing (sometimes messy, I suppose) about SQL is that you can group and order across file boundaries, not like join logicals.

As to an example of nested table expression that is equivalent to the common one (using WITH) that is sort of what you are doing and leaving 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 basically SELECT 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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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

This mailing list archive is Copyright 1997-2024 by midrange.com 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.