Re: Complex (for me) SQL question

Hi Michael,

This free eBook should help learn LATERAL joins:

https://drive.google.com/file/d/0B86kLoHq8E_PeVlhNmZORmpBUjA/view?usp=sharing

Notes regarding choosing between LATERAL joins versus ROW_NUMBER():
---------------------------------------------------------------------------------------------------------
* Many times in complex SQL with more than one ROW_NUMBER function call in
the same query, I've seen the database engine choke with an SQL0901 error.
I've never seen that happen with only a single ROW_NUMBER() function call.
Hence, beware of using more than one ROW_NUMBER() function call in the same
query. It is possible that recent PTFs have fixed this, or V7R2 has fixed
this, because I've not tried it recently, and still avoid multiple
ROW_NUMBER() references in the same query.
* On the other hand, I've used many LATERAL joins in the same query many
times, without seeing an SQL0901 error trend. DB2's coding for LATERAL
joins is more solid (time for the ROW_NUMBER() coder to buy the LATERAL
join coder lunch and talk shop :)
* If every row processed by a ROW_NUMBER() PARTITION BY set is going to be
included and consumed, ROW_NUMBER() may be the better way to go. Or, if a
row number truly needs to be assigned, then ROW_NUMBER() is the way to go.
* If only some rows are to be included and consumed, LATERAL joins are the
better choice in my opinion. For example, Dan's requirement was to pick
only the first two duplicate rows. If there were 3 or 4 duplicates or
more, those extra duplicate rows get discarded. LATERAL using FETCH FIRST
2 ROWS ONLY hopefully will be smart enough to not read those extra
duplicates, whereas I anticipate that ROW_NUMBER() will assign a row number
to those extra duplicate rows, only for them to be discarded later by a
subsequent filter (seems wasteful to me).

I use LATERAL joins a LOT. I've only noticed one thing that I think may be
considered a defect related to LATERAL.

This will choke:
-------------------------------------
from TABLE_A A
cross join lateral (
select B.COL_B
from TABLE_B B
where B.KEY_COL = A.KEY_COL
order by B.SOME_COL, A.SOME_COL --DB2 doesn't like a reference to a table
A column (above/outside the query) in the LATERAL query ORDER BY clause
fetch first row only
) as B

--It doesn't appear to matter if the LATERAL join is INNER, LEFT, RIGHT, or
CROSS, they all choke with an ORDER BY like the above.

Needs to be coded like this instead:
-----------------------------------------------
from TABLE_A A
cross join lateral (
select B.COL_B
from TABLE_B B
cross join lateral ( values A.SOME_COL ) as C ( COPY_SOME_COL ) --copy
A.SOME_COL into the LATERAL query intermediate/inner result set
where B.KEY_COL = A.KEY_COL
order by B.SOME_COL, C.COPY_SOME_COL --refer to the local / inner copy of
A.SOME_COL instead
fetch first row only
) as B

It is very rare for me to need to do ordering like that, but it has
occurred a few times, and the above work around functioned as desired.

Mike

date: Mon, 18 Jan 2016 16:29:37 -0500

from: Michael Schutte <mschutte369@xxxxxxxxx>
subject: Re: Complex (for me) SQL question

I'm gonna have to learn this.

On Mon, Jan 18, 2016 at 4:24 PM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

Hi Dan,

LATERAL joins make that simple: