RE: Using LATERAL for intermediate results

Big Storage Barn.
Be careful what you wish for.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of darren@xxxxxxxxx
Sent: Friday, July 17, 2015 6:10 AM
To: Midrange Systems Technical Discussion
Subject: RE: Using LATERAL for intermediate results

That is exactly it! I don't know how many times I've either duplicated subselects in both the select and where clause, or I've created common table expressions just to reduce the duplication. This might not change my life, but its like getting that big storage barn I've always wanted.




From: Jim It <jim_it@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/16/2015 04:30 PM
Subject: RE: Using LATERAL for intermediate results
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

I just saw a discussion about the LATERAL syntax in SQL. So, its

function

is to allow a joined table to take into account values from previously
listed tables. As I googled around, I saw some other databases using
it

in

a way that DB2 for i doesn't allow, so I wonder if there is a way to
make it work that someone else might have found.


Simple example that works in another database, but not ours:

select CUST, AMOUNT, HalfAmount
from INVOICE,
lateral (select AMOUNT/2 as HalfAmount) T1


This sort of works, but you can't refer to HalfAmount in another
calculation, which is the point of this excercise:
select *
from INVOICE,
lateral (values(INVOICE.ramt/2)) halflist

Darren,

the statement just needs a little tweaking to make it work for DB2.

create table qtemp.INVOICE
(CUST Integer,
AMOUNT Decimal(9,2));

insert into qtemp.INVOICE
(Values (111111, 67.87));
select CUST, AMOUNT, HalfAmount
from qtemp.INVOICE,
lateral
(values AMOUNT/2) T1 (HalfAmount)

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