user2602096 user2602096 - 6 months ago 21
SQL Question

Nested select query in sql server

I am working on migration of my database from Oracle to Sql Server.
I have a table definition as follows (in MS SQL Server 2012):

CREATE TABLE DEMO_TABLE(
ID BIGINT IDENTITY(1,1) NOT NULL,
COLUMN_1 BIGINT NULL,
COLUMN_2 VARCHAR(256) DEFAULT NULL,
DATE_CREATED DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET() NULL,
OPERATIONS NUMERIC(10,0) DEFAULT 0 NULL,
CONSTRAINT PK_DEMO_TABLE PRIMARY KEY (ID)
)


which works fine.
Now, I have a select statement in Oracle as follows:

select * from (select
COLUMN_1,
COLUMN_2,
DATE_CREATED,
OPERATIONS,
SYSTIMESTAMP,
row_number()
over (order by date_created DESC) rn
from
DEMO_TABLE
where
COLUMN_1 = 3 AND
OPERATIONS IN
(1,2,3,4)
AND COLUMN_2='sometext'
AND DATE_CREATED <= SYSTIMESTAMP
) where rn between 1 and 100 order by rn


I am facing problems in converting this select statement from Oracle to SQL server.
This query works fine in Oracle, but gives the following error in SQL Server:

Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'where'.


I can't figure out what is the problem here. Please help!

Answer
select * from 
(select 
            COLUMN_1,
            COLUMN_2,
            DATE_CREATED,
            OPERATIONS,
            SYSTIMESTAMP,
            row_number()
        over (order by date_created DESC) rn 
        from 
            DEMO_TABLE 
        where 
            COLUMN_1 = 3 AND 
            OPERATIONS IN 
            (1,2,3,4)
            AND COLUMN_2='sometext'
            AND DATE_CREATED <= SYSTIMESTAMP        
        ) r
        where r.rn between 1 and 100 order by r.rn
Comments