CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-database
FullStack
FullStack

Reputation: 665

How to Convert/Migrate MS-SQL Server SELECT Query To Oracle & MySQL?

In our product, we are extending support to Oracle & MySQL, so can anyone please help to migrate the following sample SQL query which works fine with MS-SQL Server, I already tried at my end but somehow it's not working for Oracle/MySQL, any help much appreciated & will convert rest of the queries by myself, thank you.

SELECT  A.SERVERID,A.DATAID 
                ,A.CREATETIMESTAMP AS 'Date Time'
                ,A.OBJECTINSTNAME
                ,A.PROJECTNAME 
                ,TEMP_IND_1.TEMP_ROW_NUM FROM DATALOG AS A WITH (NOLOCK) INNER JOIN
                ( 
                    SELECT      DATAID,ROW_NUMBER() OVER(ORDER BY CREATETIMESTAMP DESC) AS TEMP_ROW_NUM FROM    DATALOG WITH (NOLOCK)

            WHERE PROJECTNAME='ProjectA'
            ) AS TEMP_IND_1 ON A.DATAID = TEMP_IND_1.DATAID

         WHERE TEMP_IND_1.TEMP_ROW_NUM BETWEEN 1 AND 50;

Upvotes: 0

Views: 318

Answers (1)

Eldar
Eldar

Reputation: 10790

You can use same query with removing WITH (NOLOCK) parts. As they have no effect in oracle and you don't need them in oracle. and also column alias is given without as keyword and column aliases has to be double quotes. So your query becomes like this :

SELECT  A.SERVERID,A.DATAID 
                ,A.CREATETIMESTAMP  "Date Time"
                ,A.OBJECTINSTNAME
                ,A.PROJECTNAME 
                ,TEMP_IND_1.TEMP_ROW_NUM FROM DATALOG A INNER JOIN
                ( 
                    SELECT DATAID,
                           ROW_NUMBER() OVER(ORDER BY CREATETIMESTAMP DESC) TEMP_ROW_NUM 
                           FROM    DATALOG 
            WHERE PROJECTNAME='ProjectA'
            ) TEMP_IND_1 ON A.DATAID = TEMP_IND_1.DATAID
         WHERE TEMP_IND_1.TEMP_ROW_NUM BETWEEN 1 AND 50;

EDIT:

For mysql there only thing you need to do alter session to set isolation level to read uncommited before executing your original query with out with (no lock) expressions.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
 -- your query without no lock expressions
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; -- set back to original isolation level

Upvotes: 4

Related Questions