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