Re: SQL SUM(fieldname) - can I use CASE??

Yeah, I found it interesting too. It appears to me the he is building a
table on the fly with the VALUES on the LEFT JOIN LATERAL. Then basically
it's just two tables joining together. I would imagine an index would need
to be had for the field that you are joining with though.

I tried it here and ran kinda slow but I know there was no index on the
field I was joining with.


On Mon, Dec 3, 2012 at 10:57 AM, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx>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.

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

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