Help needed to convert query from Oracle to SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help needed to convert query from Oracle to SQL?

Author

Topic

Harmonygirl
Starting Member

30 Posts

Posted - 2010-07-07 : 11:32:46

Hi everyone,

I have a working query from a colleague who uses Oracle, and I would like to use this same query in SQLServer.

This query uses TRUNC and DUAL commands, and I cannot seem to find an equivalent way to do this in SQLServer.

Here is the query:


Select CalDt, Count (BG_BUG_ID) From 
 
    (Select CalDt, BG_BUG_ID, Max(AU_TIME) as Latest_Change From 

        (Select CalDt, BG_BUG_ID, AU_TIME FROM 

            (select trunc(current_date) - 1*(level-1) CalDt from DUAL connect by level <= 30) 
        left join 
            (Select BG_BUG_ID, AU_TIME from 
            AUDIT_LOG, AUDIT_PROPERTIES, BUG 
            Where AU_ENTITY_TYPE = 'BUG' 
            And AU_ENTITY_ID = BG_BUG_ID 
            And AU_ACTION_ID = AP_ACTION_ID 
            AND AP_FIELD_NAME = 'BG_STATUS' 
            --AND BG_PROJECT = '@PROJECT_NAME@'
            ) 
 
        On AU_TIME <= CalDt) 
 
    Group By CalDt, BG_BUG_ID) results, AUDIT_LOG, AUDIT_PROPERTIES 

where AU_ENTITY_TYPE = 'BUG' 
AND AP_FIELD_NAME = 'BG_STATUS' 
AND AU_ENTITY_ID = results.BG_BUG_ID 
AND AU_TIME = results.Latest_Change 
AND AU_ACTION_ID = AP_ACTION_ID 
AND AP_NEW_VALUE in ('Open', 'New') 

Group by CalDt 
Order By CalDt Asc

Thanks in advance for your help and time.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-07 : 14:27:27

DUAL is the name of the table, where you are reading/getting data from - usually consist of single column and single row. it is usually used as a Dummy table for testing several different functions and server for variety of purposes.

TRUNC is oracle function for casting date format data. similar to CAST/CONVERT in Transact SQL

Harmonygirl
Starting Member

30 Posts

Posted - 2010-07-07 : 15:12:44

Thanks for the reply.

Can you please provide the syntax that I would need to change in that line above? I've been trying on my own and not getting any good results.

quote:
Originally posted by slimt_slimt

DUAL is the name of the table, where you are reading/getting data from - usually consist of single column and single row. it is usually used as a Dummy table for testing several different functions and server for variety of purposes.

TRUNC is oracle function for casting date format data. similar to CAST/CONVERT in Transact SQL

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-07-08 : 04:28:24

I am not an Oracle expert but it looks as though

select trunc(current_date) - 1*(level-1) CalDt from DUAL connect by level <= 30

is meant to produce a list of the last 30 days.
If this is so, you will need to use a number/tally table in t-sql.
The following uses the list of numbers in master.dbo.spt_values, but you may want to build your own table.


SELECT DATEADD(d, DATEDIFF(D, 0, GETDATE()), 0) - 1 * (N.number - 1) AS CalDT
FROM master.dbo.spt_values N
WHERE N.type = 'P' and N.number BETWEEN 1 AND 30

Harmonygirl
Starting Member

30 Posts

Posted - 2010-07-09 : 09:29:10

OK thanks, I added this new line based on your suggestions, but I'm still getting a syntax error (although further along in the query). Here is my revised code - new line is in green, and the point of the syntax error is at the word "ON" - highlighted in red:



Select CalDt, Count (BG_BUG_ID) From
         (Select CalDt, BG_BUG_ID, Max(AU_TIME) as Latest_Change From
                 (Select CalDt, BG_BUG_ID, AU_TIME FROM
                       (SELECT DATEADD(d, DATEDIFF(D, 0, GETDATE()), 0) - 1 * (N.number - 1) AS CalDt FROM audit_log where DATEPART(YEAR, au_time) = '2010') results_1
        left join
            (Select BG_BUG_ID, AU_TIME from
            AUDIT_LOG, AUDIT_PROPERTIES, BUG
            Where AU_ENTITY_TYPE = 'BUG'
            And AU_ENTITY_ID = BG_BUG_ID
            And AU_ACTION_ID = AP_ACTION_ID
            AND AP_FIELD_NAME = 'BG_STATUS'
            )
         ON AU_TIME <= CalDt)

    Group By CalDt, BG_BUG_ID) results, AUDIT_LOG, AUDIT_PROPERTIES

where AU_ENTITY_TYPE = 'BUG'
AND AP_FIELD_NAME = 'BG_STATUS'
AND AU_ENTITY_ID = results.BG_BUG_ID
AND AU_TIME = results.Latest_Change
AND AU_ACTION_ID = AP_ACTION_ID
AND AP_NEW_VALUE in ('Open', 'New')

Group by CalDt
Order By CalDt Asc

quote:
Originally posted by Ifor

I am not an Oracle expert but it looks as though

select trunc(current_date) - 1*(level-1) CalDt from DUAL connect by level <= 30

is meant to produce a list of the last 30 days.
If this is so, you will need to use a number/tally table in t-sql.
The following uses the list of numbers in master.dbo.spt_values, but you may want to build your own table.

SELECT DATEADD(d, DATEDIFF(D, 0, GETDATE()), 0) - 1 * (N.number - 1) AS CalDT
FROM master.dbo.spt_values N
WHERE N.type = 'P' and N.number BETWEEN 1 AND 30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources