Re: Using LATERAL for intermediate results

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)