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...
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.
Sum(NEWVALUE) As NEWVALUE
QTEMP.SUMME As A
Left Join Lateral
(Values ('A', Value),
('B', -Value)) As B (ITEMGRP, NEWVALUE)
A.ITEMGRP = B.ITEMGRP
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
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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives