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.

HTH
Vern

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
(Values
('AAAAAA', 'A', 100.00),
('AAAAAA', 'B', 150.00)) With Data
The statement to summary the data would look like this.
Select
ITEM,
Sum(NEWVALUE) As NEWVALUE
From
QTEMP.SUMME As A
Left Join Lateral
(Values ('A', Value),
('B', -Value)) As B (ITEMGRP, NEWVALUE)
On
A.ITEMGRP = B.ITEMGRP
Group By
ITEM
Returns a summary amount for item 'AAAAA' = -50.00.
Put both statements into VE and see which one is faster.
Jim

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
article:

http://www.mcpressonline.com/sql/the-case-for-sql-case.html

(Gratuitous plug on my part).

Sam

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.