|
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 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.