Re: Correlated Subqueries in the FROM clause

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Birgitta,

That doesn't quite work, I'm looking for the lowest price for a given
gppc.  There may be one record for a gppc, or 100, thus the FETCH FIRST.

I can do it as a CTE, in fact that was an alternative solution I tested.

But I have to use
  row_number() over(partition by gppc order by gppc, acq_cost) as rownbr

then select rownbr=1 instead of being able to use fetch first.

It works, but takes more time as there's a lot more data that end up not
being used.  Probably could have improved it by limiting lowprc to only
gppcs in list.  But lateral fixed the subquery.

If I get some time, I may go back and play with this some more just for
future reference.

Charles



On Fri, Jul 17, 2015 at 1:01 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> Why would you have a sub-select in the from clause of the final select
> statement?
> Why not simply using a second CTE?
>
> Something like this:
> with list as (select line, ndcupc, gppc
>                  from tempsav.cmw
>                        left outer join medispan.mf2ndc on ndc = ndcupc ),
>
>      LowPrc as (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)
>
> select * from list L
>    left outer join lowprc
>               on L.gppc = lowprc.gppc
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
> Charles Wilt
> Gesendet: Thursday, 16.7 2015 19:41
> An: Midrange Systems Technical Discussion
> Betreff: Correlated Subqueries in the FROM clause
>
> Can I have a correlated subquery in the FROM clause for DB2 for i?
>
> This doesn't seem to be working...
>
> 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 (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
> ;
> --
> 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.