Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server to Oracle Query Conversion Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 11:16 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:43 AM
Points: 11, Visits: 25
Hi All,
I have one query in SQL server. I want to convert that query to Oracle. Well i am not that good with writing queries in Oracle. Please help. Following is the query in SQL server.

DECLARE @StartYear AS INT = 2010;
DECLARE @EndYear AS INT = 2014;

WITH
years
AS (SELECT YYYY = @StartYear
UNION ALL
SELECT yyyy + 1
FROM years
WHERE yyyy < @EndYear)
,months
AS (SELECT MM = 1
UNION ALL
SELECT mm + 1
FROM months
WHERE mm < 12)
,days
AS (SELECT DD = 1
UNION ALL
SELECT dd + 1
FROM days
WHERE dd < 31)
,datesraw
AS (SELECT YYYY = yyyy,
MM = mm,
DD = dd,
ID_Date = yyyy * 10000 + mm * 100 + dd,
Date = CASE
WHEN Isdate(yyyy * 10000 + mm * 100 + dd) = 1 THEN Cast
( Cast(yyyy * 10000 + mm * 100 + dd AS VARCHAR) AS DATE)
ELSE NULL
END
FROM years
CROSS JOIN months
CROSS JOIN days
WHERE Isdate(yyyy * 10000 + mm * 100 + dd) = 1)
SELECT d.id_date,
d.date,
[Year] = Year(d.date),
MonthNumber = Month(d.date),
[Month] = Datename(month, d.date),
DayOfMonth = Day(d.date),
DayOfWeekNumber = Datepart(dw, d.date),
[DayOfWeek] = Datename(dw, d.date),
WorkingDay = Cast(CASE Datepart(dw, d.date)
WHEN 1 THEN 0 -- Sunday
WHEN 7 THEN 0 -- Saturday
ELSE 1 -- Might lookup for a holidays table here
END AS BIT)
FROM datesraw d
ORDER BY d.date


Which Gives output in following format.
id_date date Year MonthNumber Month DayOfMonth DayOfWeekNumber DayOfWeek WorkingDay
20100101 1/1/2010 2010 1 January 1 6 Friday 1
20100102 1/2/2010 2010 1 January 2 7 Saturday 0
20100103 1/3/2010 2010 1 January 3 1 Sunday 0
20100104 1/4/2010 2010 1 January 4 2 Monday 1
20100105 1/5/2010 2010 1 January 5 3 Tuesday 1


Thanks in Advance
Post #1489522
Posted Thursday, August 29, 2013 2:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
The easiest method to do this is to run this query on Oracle and see which functions error out.
Use Google to find the Oracle equivalent. (for example: convert sql server versus oracle).
And check if Oracle supports CTEs :)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489596
Posted Friday, August 30, 2013 12:28 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:43 AM
Points: 11, Visits: 25
Thanks for your suggestion.
Somehow i managed to write query in Oracle.
Following is my query.

WITH years AS (
SELECT ROWNUM rn
FROM dual
CONNECT BY LEVEL <= (select round( SYSDATE-to_date('2010-01-01','YYYY-MM-DD')) from dual))
Select
to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') * 10000 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'mm') * 100 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As Date_ID
,to_date('2010-01-01','YYYY-MM-DD')-1 +rn As "Date"
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') As Year
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'MM') As Month_Number
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Month') As Month
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As DayOfMonth
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Day') As DayOfWeek
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'D') As DayOfWeekNo
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Q') As Quarter
From years;



If you run this query you will see my expected output.

Now my concern is i don't want to hard code date. In above query i have hard coded the date '2010-01-01'.
Can we somehow use it from variable?
Post #1490009
Posted Friday, August 30, 2013 1:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
And now is the time to go to an Oracle forum, as you're now asking PL/SQL questions



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1490012
Posted Friday, August 30, 2013 3:50 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:43 AM
Points: 11, Visits: 25
Got solution for this on Oracle forum.

var start_date varchar2(10)
exec :start_date := '2010-01-01'

WITH years AS (
SELECT to_date(:start_date,'YYYY-MM-DD') -1 +level dt
FROM dual
CONNECT BY LEVEL <= (select round( SYSDATE-to_date(:start_date,'YYYY-MM-DD')) from dual)
)
Select
to_char(dt ,'yyyy') * 10000 + to_char(dt,'mm') * 100 + to_char(dt,'dd') As Date_ID
,dt As "Date"
,to_char(dt ,'yyyy') As Year
,to_char(dt,'MM') As Month_Number
,to_char(dt,'Month') As Month
,to_char(dt,'dd') As DayOfMonth
,to_char(dt,'Day') As DayOfWeek
,to_char(dt,'D') As DayOfWeekNo
,to_char(dt,'Q') As Quarter
From years;

Thanks for your posts.
Post #1490052
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse