Solved

Convert  oracle query to ms sql server

Posted on 2012-03-19
11
503 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

777 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