• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

Convert oracle query to ms sql server

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
nrajasekhar7
Asked:
nrajasekhar7
  • 4
  • 4
  • 2
  • +1
1 Solution
 
b_levittCommented:
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
 
nrajasekhar7Author Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
DavidSenior Oracle Database AdministratorCommented:
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
 
b_levittCommented:
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
 
nrajasekhar7Author Commented:
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
 
b_levittCommented:
My guess is that your @startdate or @enddate var is declared as a varchar instead of a date time.
0
 
nrajasekhar7Author Commented:
in the database column datatype is datetime only .
But tried still getting the same error , any other resolution should  i try !!
Please suggest.
0
 
b_levittCommented:
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
 
nrajasekhar7Author Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
Closure is premature, the asker is suggesting his/her own comment as the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now