Re: Correlated Subqueries in the FROM clause

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Ok, lateral solved the issue.

I just had to add the correlation back in...I'd taken it out initially
because I was getting an error.

On Thu, Jul 16, 2015 at 3:09 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

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