Re: Correlated Subqueries in the FROM clause

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Nope still returning all nulls for the fields from the subquery...



On Thu, Jul 16, 2015 at 2:01 PM, Jim It <jim_it@xxxxxxxxxxx> wrote:

> Charles,
>
> Try the lateral key word before subquery. I just spent the morning
> teaching about lateral correlation.
>
> with list as (select line, ndcupc, gppc
> from tempsav.cmw
> left outer join medispan.mf2ndc on ndc = ndcupc )
> select *
> from list L
> left outer join lateral (select ndcupc, idescr
> , olsmlprg.calccustacqcost(decimal(150,6,0),
> iitnbr) as acq_cost
> , gppc
> from medispan.mlitems M
> left outer join
> olsdta.ioritemmst using (iitnbr)
> where gppc = m.gppc
> order by 3
> fetch first row only) as lowprc
> on L.gppc = lowprc.gppc
>
> Jim
> --
> 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.