February 1st, 2012, 04:31 PM
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
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.
February 1st, 2012, 04:48 PM
What exactly does OUTER APPLY do?
Isn't that just a LEFT OUTER JOIN?