#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    3
    Rep Power
    0

    SQL Server query to be convert it to Oracle 10g


    DECLARE @MainTable TABLE (UniqueID INTEGER, Category VARCHAR(200), WeekDate DATETIME, VALUE INTEGER)

    INSERT INTO @MainTable VALUES(123, 'Shirts', '10/07/2011', 5000)

    INSERT INTO @MainTable VALUES(123, 'Shirts', '10/14/2011', 8000)

    INSERT INTO @MainTable VALUES(124, 'Pants', '10/07/2011', 4000)

    INSERT INTO @MainTable VALUES(125, 'Shorts', '10/14/2011', 8000)

    INSERT INTO @MainTable VALUES(126, 'Shoes', '10/21/2011', 9000);

    --select * from @MainTable;

    WITH Dates AS

    (

    SELECT DISTINCT WeekDate FROM @MainTable
    ),

    Categories AS (SELECT DISTINCT Category FROM @MainTable),

    AllInfo AS (SELECT WeekDate, Category FROM Dates, Categories)

    SELECT d.UniqueID, A.Category,d.Value, A.WeekDate
    INTO #Results
    FROM AllInfo A LEFT JOIN @MainTable d

    ON d.WeekDate = A.weekDate AND A.Category = d.Category

    ORDER BY A.Category, A.WeekDate

    SELECT R.UniqueID, R.Category, COALESCE(R.VALUE, Cor.VALUE) AS VALUE, R.WeekDate
    FROM #Results R
    OUTER APPLY (SELECT TOP (1) Value FROM #Results R1 WHERE R1.Category = R.Category
    AND R1.WeekDate <=R.WeekDate AND R1.Value IS NOT NULL ORDER BY WeekDate) Cor
    ORDER BY R.Category, R.WeekDate

    The query works with all the CTEs up to the last select statement. Oracle does not support the OUTER APPLY statement, would anybody know how should the last piece be written to make it work in Oracle?

    Thanks very much for you help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    What exactly does OUTER APPLY do?

    Isn't that just a LEFT OUTER JOIN?

IMN logo majestic logo threadwatch logo seochat tools logo