× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.