Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server to Oracle Query Conversion


SQL Server to Oracle Query Conversion

Author
Message
roshan.zanwar
roshan.zanwar
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 36
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16447 Visits: 13202
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 Smile



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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
roshan.zanwar
roshan.zanwar
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 36
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?

Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16447 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
roshan.zanwar
roshan.zanwar
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 36
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. :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search