Solved

Convert  oracle query to ms sql server

Posted on 2012-03-19
11
497 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now