Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert  oracle query to ms sql server

Posted on 2012-03-19
11
Medium Priority
?
536 Views
Last Modified: 2012-03-28
Hi

Recently we  have changed our database to ms- sql server,
I need to change my query to run in ms sql server ,
What is the Substitution of DBA_Objects in Ms -sql server


select concat('% Cases with Recovery time <  ',:recovery) as Label,
to_char(:param1+ ROWNUM-1,'DD-MM-YY') as month1,
to_date(:param1,'DD/MM/YYYY')+ ROWNUM-1 as month2,
:param1,:param2,
0 as value
FROM   DBA_OBJECTS
WHERE :param1+ ROWNUM-1 BETWEEN :param1 AND :param2
order by 3

Please suggest.

Thanks in Advance.
0
Comment
Question by:nrajasekhar7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37736648
0
 
LVL 11

Expert Comment

by:b_levitt
ID: 37737568
I'm not sure what you're doing in your query since most of it references bind variables or rownum, but the closest equivalent to dba_objects, i'd guess to be sys.objects. http://msdn.microsoft.com/en-us/library/ms190324.aspx

Although I'm going to also assume that dba_objects has more information in it so you may need to union other sys tables or the information_schema tables.

B
0
 

Author Comment

by:nrajasekhar7
ID: 37740516
Please find my complete Query :

SELECT
             CONVERT(VARCHAR(23), a.CREATED_TIME) as day,
             CONVERT(DATETIME, CONVERT(VARCHAR(23), a.CREATED_TIME, 103), 103) as day_num,
             SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) as VALUE,
             'Minor Accidents' as ACCIDENT_DETAILS,
             SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) * 100 / (SELECT (
                              case (SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)))
                                     when 0  then 1
                                     else (SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)))
                               end) as tot_sum
            FROM  situation_arc a INNER JOIN situation_accident_arc b ON a.situation_id  = b.situation_id
             INNER JOIN situation_kpe_arc k ON a.situation_id  = k.situation_id  
            WHERE       CONVERT(VARCHAR(23), a.created_time, 101)  between CONVERT(VARCHAR(23), '09/13/2011', 101)
             and  CONVERT(VARCHAR(23), '09/16/2011', 101)
             AND      a.ACKNOWLEDGED_BY  is not null
             AND      k.START_TIME  != k.END_TIME
             AND      k.ITRANSPORT_ID  <= 0
) as tot_percentage
FROM  situation_arc a INNER JOIN situation_accident_arc b ON a.situation_id  = b.situation_id  
INNER JOIN situation_kpe_arc k ON a.situation_id  = k.situation_id  
WHERE       CONVERT(VARCHAR(23), a.created_time, 101)  between CONVERT(VARCHAR(23), '09/13/2011', 101)
 and  CONVERT(VARCHAR(23), '09/16/2011', 101)
 AND      a.ACKNOWLEDGED_BY  is not null
 AND      k.START_TIME  != k.END_TIME
 AND      k.ITRANSPORT_ID  <= 0
GROUP BY CONVERT(VARCHAR(23), a.CREATED_TIME),
        CONVERT(DATETIME, CONVERT(VARCHAR(23), a.CREATED_TIME, 103), 103)
UNION ALL
 SELECT
             CONVERT(VARCHAR(23), a.CREATED_TIME) as day,
             CONVERT(DATETIME, CONVERT(VARCHAR(23), a.CREATED_TIME, 103), 103) as day_num,
             SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) as VALUE,
             'Accident with Injury' as ACCIDENT_DETAILS,
              SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) * 100 / (SELECT (
                              case (SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)))
                                     when 0  then 1
                                     else (SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)))
                               end) as tot_sum
            FROM  situation_arc a INNER JOIN situation_accident_arc b ON a.situation_id  = b.situation_id  
            INNER JOIN situation_kpe_arc k ON a.situation_id  = k.situation_id  
            WHERE       CONVERT(VARCHAR(23), a.created_time, 101)  between CONVERT(VARCHAR(23), '09/13/2011', 101)  
            and  CONVERT(VARCHAR(23),'09/16/2011', 101)
             AND      a.ACKNOWLEDGED_BY  is not null
             AND      k.START_TIME  != k.END_TIME
             AND      k.ITRANSPORT_ID  <= 0
) as tot_percentage
FROM  situation_arc a INNER JOIN situation_accident_arc b ON a.situation_id  = b.situation_id  
INNER JOIN situation_kpe_arc k ON a.situation_id  = k.situation_id  
WHERE       CONVERT(VARCHAR(23), a.created_time, 101)  between CONVERT(VARCHAR(23),'09/13/2011', 101)
 and  CONVERT(VARCHAR(23), '09/16/2011', 101)
 AND      a.ACKNOWLEDGED_BY  is not null
 AND      k.START_TIME  != k.END_TIME
 AND      k.ITRANSPORT_ID  <= 0
GROUP BY CONVERT(VARCHAR(23), a.CREATED_TIME),
        CONVERT(DATETIME, CONVERT(VARCHAR(23), a.CREATED_TIME, 103), 103)
UNION ALL
 SELECT
             CONVERT(VARCHAR(23), a.CREATED_TIME) as day,
             CONVERT(DATETIME, CONVERT(VARCHAR(23), a.CREATED_TIME, 103), 103) as day_num,
             SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)) as VALUE,
             'Accident with Fatalities' as ACCIDENT_DETAILS,
              SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)) * 100 / (SELECT (
                              case (SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)))
                                     when 0  then 1
                                     else (SUM(CONVERT(FLOAT, b.NUMBEROFSLIGHTINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFSERIOUSINJURIES)) + SUM(CONVERT(FLOAT, b.NUMBEROFFATALITIES)))
                               end) as tot_sum
            FROM  situation_arc a INNER JOIN situation_accident_arc b ON a.situation_id  = b.situation_id  
            INNER JOIN situation_kpe_arc k ON a.situation_id  = k.situation_id  
            WHERE       CONVERT(VARCHAR(23), a.created_time, 101)  between CONVERT(VARCHAR(23), '09/13/2011', 101)  
            and  CONVERT(VARCHAR(23), '09/16/2011', 101)
             AND      a.ACKNOWLEDGED_BY  is not null
             AND      k.START_TIME  != k.END_TIME
             AND      k.ITRANSPORT_ID  <= 0
) as tot_percentage
FROM  situation_arc a INNER JOIN situation_accident_arc b ON a.situation_id  = b.situation_id  
INNER JOIN situation_kpe_arc k ON a.situation_id  = k.situation_id  
WHERE       CONVERT(VARCHAR(23), a.created_time, 101)  between CONVERT(VARCHAR(23), '09/13/2011', 101)  
and  CONVERT(VARCHAR(23), '09/16/2011', 101)
 AND      a.ACKNOWLEDGED_BY  is not null
 AND      k.START_TIME  != k.END_TIME
 AND      k.ITRANSPORT_ID  <= 0
GROUP BY CONVERT(VARCHAR(23), a.CREATED_TIME),
        CONVERT(DATETIME, CONVERT(VARCHAR(23), a.CREATED_TIME, 103), 103)
UNION ALL
 SELECT
             CONVERT(VARCHAR(23), '09/13/2011' + ROWNUM -1) as day,
             CONVERT(DATETIME, '09/13/2011', 103) + ROWNUM -1 as day_num,
             0 as VALUE,
             'Accident with Fatalities' as ACCIDENT_DETAILS,
             0 as tot_percentage
FROM  sys.objects
WHERE      '09/13/2011' + ROWNUM - 1  BETWEEN '09/13/2011'  AND  '09/14/2011'
 ORDER BY 2

Iam getting error :  invaild column name for ROWNUM:
if i use dba_object then iam getting error invalid object name.

As iam new new ms-sql server ,Please suggest
What should i use in my Last Select statement.
I  need Urgent ,Please help to resolve these.

Thanks in Advance.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 23

Expert Comment

by:David
ID: 37742261
Only a guess, but perhaps you need extra parentheses to set up the day_num correctly.  You have: CONVERT(DATETIME, '09/13/2011', 103) + ROWNUM -1 as day_num,
0
 
LVL 11

Expert Comment

by:b_levitt
ID: 37742482
The union all throws me off, but I think you're just trying to generate a date dimension table and in that case ANY table with enough rows to satisfy your range would work.  For example:

declare @startDate DateTime
declare @endDate DateTime
set @startDate = '9/13/2011'
set @endDate = '9/14/2011'

SELECT
             CONVERT(VARCHAR(23), @startDate + ROWNUM -1) as day,
             CONVERT(DATETIME, @startDate, 103) + ROWNUM -1 as day_num,
             0 as VALUE,
             'Accident with Fatalities' as ACCIDENT_DETAILS,
             0 as tot_percentage
FROM  (
	select row_number() over (order by object_id asc) as rownum
	from sys.objects
) dim
WHERE      @startDate + ROWNUM - 1  BETWEEN @startDate  AND  @endDate
 ORDER BY 2

Open in new window


A better option (but one that might be beyond the scope of this question at the moment) might be to generate a date dimension table with a recursive CTE

declare @startDate DateTime
declare @endDate DateTime
set @startDate = '9/13/2011'
set @endDate = '9/15/2011'; 

with DateDim as
(
	select @startDate [Date]
	union all
	select [Date]+1
	from DateDim 
	where [Date] between  @startDate and @endDate
)
select *
from DateDim
OPTION (MAXRECURSION 0) 

Open in new window

0
 

Author Comment

by:nrajasekhar7
ID: 37746208
I have tried these :SELECT
             CONVERT(VARCHAR(23), @startDate + ROWNUM -1) as day,
             CONVERT(DATETIME, @startDate, 103) + ROWNUM -1 as day_num,
             0 as VALUE,
             'Accident with Fatalities' as ACCIDENT_DETAILS,
             0 as tot_percentage
FROM  (
      select row_number() over (order by object_id asc) as rownum
      from sys.objects
) dim
WHERE      @startDate + ROWNUM - 1  BETWEEN @startDate  AND  @endDate
 ORDER BY 2
 
I am getting the Below error ,
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

I tried do with Isnumeric also but it dint work, Please suggest.
Thanks in Advance
0
 
LVL 11

Expert Comment

by:b_levitt
ID: 37747393
My guess is that your @startdate or @enddate var is declared as a varchar instead of a date time.
0
 

Author Comment

by:nrajasekhar7
ID: 37750774
in the database column datatype is datetime only .
But tried still getting the same error , any other resolution should  i try !!
Please suggest.
0
 
LVL 11

Accepted Solution

by:
b_levitt earned 2000 total points
ID: 37752273
Does the query I posted run, as-is?

declare @startDate DateTime
declare @endDate DateTime
set @startDate = '9/13/2011'
set @endDate = '9/14/2011'

SELECT
             CONVERT(VARCHAR(23), @startDate + ROWNUM -1) as day,
             CONVERT(DATETIME, @startDate, 103) + ROWNUM -1 as day_num,
             0 as VALUE,
             'Accident with Fatalities' as ACCIDENT_DETAILS,
             0 as tot_percentage
FROM  (
	select row_number() over (order by object_id asc) as rownum
	from sys.objects
) dim
WHERE      @startDate + ROWNUM - 1  BETWEEN @startDate  AND  @endDate
 ORDER BY 2

Open in new window

0
 

Author Comment

by:nrajasekhar7
ID: 37764263
I've requested that this question be closed as follows:

Accepted answer: 0 points for nrajasekhar7's comment #37750774

for the following reason:

Thank you Very Much !!!
0
 
LVL 23

Expert Comment

by:David
ID: 37764264
Closure is premature, the asker is suggesting his/her own comment as the solution.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how the fundamental information of how to create a table.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question