Re: replace subquery with left join

Hello everybody

I've tested all solutions. Isaac's solution doesn't work on DB2 (maybe the reference to P is not allowed in the subquery).

The performance is still very poor. Maybe it's because the prices-table is too big and complicated. (But I can't change it: it's from our ERP). Let me explain:

The relevant fields are:

hrkn CHAR(10) hierachyknot (=branch) prlist CHAR(8) pricelist
valdate DEC(5,0) valid date

cur       CHAR(4)   currency
itemno    CHAR(20)  itemnumber
prline    CHAR(2)   priceline
scale     DEC(9,3)  scale

So each branch can have several pricelists and pricelines in several currencies for many quantities.

I rewrote Mike's solution to:

SELECT I.ItemNo, P.Price
 FROM items AS I
   LEFT JOIN prices AS P

     ON  '0101'     = P.HrKn
     AND '      01' = P.PrList
     AND 'EUR'      = P.Cur
     AND I.ItemNo   = P.ItemNo
     AND '01'       = P.PrLine
     AND 0          = P.Scale
 WHERE P.ValDate = (SELECT MAX(ValDate)
                     FROM prices
                     WHERE HrKn    =  P.HrKn
                       AND PrList  =  P.PrList
                       AND ValDate <= (DAYS(CURDATE())-693594)
                       AND Cur     =  P.Cur
                       AND ItemNo  =  P.ItemNo
                       AND PrLine  =  P.PrLine
                       AND Scale   =  P.Scale)

(At the moment I don't care about the NULLs ;-) and I don't think it's necassary to group by date like Anders suggested, because the ERP-System prohibits inputs for the same date)

It's awfully slow:
There are about 24000 records in the items-table and about 170000 records in the prices-table and a query lasts circa 50 min!

-Hans Received on Wed Nov 06 2002 - 17:14:27 CET