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.