Re: replace subquery with left join

I can't promiss this is faster... but it might. Heck I can't even promiss it's correct ;-)

WITH P1 AS (SELECT itemno, price, valdate

              FROM PRICES
              WHERE valdate <= (DAYS(CURDATE()) - 693594)
     P2 AS (SELECT itemno, price, valdate
                  max(valdate) over (group by itemno) as maxvaldate
             FROM P1)
     P3 AS (SELECT itemno, price
              FROM P2
              WHERE valdate = maxvaldata)

       LEFT OUTER JOIN
       prices AS P
       ON I.itemno = P.itemno;

Cheers
Serge

-- 
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/