Solved

Convert  oracle query to ms sql server

Posted on 2012-03-19
11
510 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 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