RE: my first use of lateral

Steve-

I saw that post and started playing with the LATERAL keyword to see how it could be useful, but I came to the conclusion that Common Table Experession (CTE) were much more widely known and capable solution (though less compact).

How I would code the query:

With FIRSTDATE as (select min(c.w2sdte), c.w2wacd, c.w2sid
from w2mast c
where c.w2sdte <> 0
group by c.w2wacd, c.w2sid)
select a.w1BOL, b.w2sdte
from w1mast a JOIN FIRSTDATE b ON
a.w1wacd = b.w2wacd and a.w1sid = b.w2sid
where a.w1wacd = 'LAE' and a.w1BOL <> ' '

I would be curious to know if there is any difference in performance between these two solutions


-Tom Stieger
IT Manager
California Fine Wire


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Steve Richter
Sent: Friday, December 14, 2012 11:03 AM
To: Midrange Systems Technical Discussion
Subject: my first use of lateral

someone posted about sql lateral recently, which I had never heard of.
Here is my first use of it.

There are 2 files. A header and a detail. Header contains the BOL number.
The detail contains the ship date of all the lines of that BOL. ( go figure )

Here is the sql that shows the BOL and its ship date:

select a.w1BOL, b.w2sdte
from w1mast a
, lateral ( select c.w2sdte
from w2mast c
where a.w1wacd = c.w2wacd and a.w1sid = c.w2sid
and c.w2sdte <> 0
fetch first row only ) b
where a.w1wacd = 'LAE' and a.w1BOL <> ' '

I figure without lateral I have to do a join with group by and MIN(w2sdte) to eliminate the multiple detail rows. But with lateral I can use "fetch first row only" which is clearer to read since it does exactly what I want to be done.

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