Re: replace subquery with left join

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Thu, 07 Nov 2002 12:52:46 -0500
Message-ID: <3DCAA86E.9030104@ca.ibm.com>

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)

SELECT I.itemno, P.price
  FROM items AS I

       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/

Received on Thu Nov 07 2002 - 11:52:46 CST