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

Question

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;

Pls include what you have tried and what error message or unexpected behaviour you encountered because as your question stands it basically read: pls do my work for me for free. I know this was not your intention, but still that's how it reads. Btw, I don't think you need to modify much in this query to make it work for oracle and mysql v8. — Shadow
– Shadow, Commented Dec 21, 2019 at 8:28
@Shadow Absolutely not, it depends on how you think & how you take it before deciding the intentions, I believe, thanks for the comment though. — FullStack
– FullStack, Commented Dec 21, 2019 at 8:45

Eldar · Accepted Answer · 2019-12-21 09:21:05Z

4

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

edited Dec 21, 2019 at 9:21

answered Dec 21, 2019 at 8:28

Eldar

10.9k2 gold badges20 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FullStack Over a year ago

Thanks for the quick update, really appreciate that, it's quite insightful.

FullStack Over a year ago

would you like to add any comments for MySQL syntax please, thanks.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related