1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Server Query to convert it to Oracle 10g

Discussion in 'SQL PL/SQL' started by Midway, Feb 1, 2012.

  1. Midway

    Midway Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):
    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. club

    club Active Member

    Messages:
    31
    Likes Received:
    1
    Trophy Points:
    185
    Nice post... thanks
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I can't get your statements to work in Oracle without a lot of modification (including using explicit date formats). How did you get this to run in an Oracle database without substantial modification?
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India