Just my brain drawing unwarranted conclusions, I'm sure, but my initial "feel" of this is similar to Common Table Expression...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Monday, December 03, 2012 10:57 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL SUM(fieldname) - can I use CASE??

I had to look it up - seems it came out in V5R3. It is actually just the
LATERAL keyword - used in combination with any kind of JOIN.

Here's a brief description -

A nested table expression which is preceded by a LATERAL or TABLE
keyword will consider table-references that precede it in the FROM
clause as object tables.

So this allows for correlated references to other dials in the JOIN,
within nested table expressions, say - we used to have to put these
conditions OUTSIDE the table expressions.

At least that's my very brief first take on this.


On 12/3/2012 9:57 AM, DeLong, Eric wrote:
I've never seen "left join lateral"... It appears to have been around for some time. V5R3 ish...

Will review your suggestion. Looks like it could be useful, but it just doesn't click with me yet...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim It
Sent: Friday, November 30, 2012 8:38 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL SUM(fieldname) - can I use CASE??

I agree that case may be used to perform such tasks, but after significant testing this can be performed much, much faster, if the only calculation occurring in the query is the Sum Aggregate Function.

So, if you have a table as such,

Create Table QTEMP.SUMME (ITEM, ITEMGRP, Value) As
('AAAAAA', 'A', 100.00),
('AAAAAA', 'B', 150.00)) With Data

The statement to summary the data would look like this.

Left Join Lateral
(Values ('A', Value),
('B', -Value)) As B (ITEMGRP, NEWVALUE)
Group By

Returns a summary amount for item 'AAAAA' = -50.00.

Put both statements into VE and see which one is faster.


Date: Fri, 30 Nov 2012 19:54:54 -0500
From: lennon_s_j@xxxxxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL SUM(fieldname) - can I use CASE??

Definitely. Eric gave you the answer.

A Case expression can be used almost anywhere a value is needed. This
is extremely useful and if you want to explore it more, check out this


(Gratuitous plug on my part).


On 11/30/2012 5:41 PM, Stone, Joel wrote:
Can I sum a fieldname using the CASE stmt as follows:

when the item-group = 'A', sum as is;
when the item-group = 'B', multiply by -1 and then sum?

How is this best accomplished?
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.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot 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 here. If you have questions about this, please contact