Re: Correlated Subqueries in the FROM clause

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.