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



On 16-Jul-2015 13:29 -0600, darren wrote:

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


The Nested Table Expression (NTE) with identifier T1 is incomplete; there is no FROM clause. Both SYSIBM.SYSDUMMY1 and QSYS2.QSQPTABL serve that purpose, although a Row Values Clause (VALUES) is the newer syntax. The subquery without a FROM clause as implied single-row result is non-standard syntax; not allowed in any of the DB2 SQL variants AFaIK. The AMOUNT and HalfAmount also are left unqualified; qualified references are recommended for clarity, and the unqualified AMOUNT will also cause a SQL warning.

select CUST, AMOUNT, T1.HalfAmount
from INVOICE as I
, lateral (select I.AMOUNT/2 as HalfAmount
from qsys2.qsqptabl ) as T1


This sort of works, but you can't refer to HalfAmount in another
calculation, which is the point of this exercise:

select *
from INVOICE,
lateral (values(INVOICE.ramt/2)) halflist


The "This" seems to imply the above statement using the VALUES row clause, but the column in the expression was RAMT instead of AMOUNT and was unnamed, although the NTE was identified with HalfList; i.e. there is no HalfAmount named. Perhaps the following [with both a more explicit column-list selection asking for all columns from each table-reference vs all from the result-set, and explicit column naming hoping to make the additional result-column more conspicuous]?:

select INVOICE.*, halflist.*
from INVOICE
, lateral (values(INVOICE.ramt/2)) as halflist (HalfAmount)


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.