Re: my first use of lateral

[Steve Richter <stephenrichter@xxxxxxxxx>]

Tom, I have to figure that "fetch first row only" is more efficient than
group by. Lateral also looks useful in that it returns multiple columns. I
like using sub queries:
  select  a.ordnbr, ( select sum(b.ordqty) from orddetail b where b.ordnbr
= a.ordnbr ) ordqty
  from   OrdHeader a

but a sub query can only return a single column. I can do the same with
lateral, but return multiple columns.
  select  a.ordnbr, b.ordqty, b.nbrLines
  from   OrdHeader a
  , lateral ( select  sum(c.ordqty) ordqty, count(*) nbrLines
            from OrdDetail c
         where a.ordnbr = c.ordnbr ) b
-Steve



On Fri, Dec 14, 2012 at 2:28 PM, Tom E Stieger <testieger@xxxxxxxxxxxx>wrote:

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