asked on
Query error when migrating from Oracle to SQL Server
Good morning all!! I wonder if I could get some assistance. Im in the middle of migrating from Oracle to SQL Server, and things have gone pretty smoothly, but I ran across a small query that keeps giving me errors, and I know its something to do with restructuring it in SQL Server, but no matter what i do I cant seem to get it. The query is below and the error I get has something to do with "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified"
Any help would be greatly appreciated!!
select * from (select SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from MCA.SPEND_PLAN_HISTORY,
(SELECT MAX(SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE) AS latestdate
FROM MCA.SPEND_PLAN_HISTORY
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345') LATESTRESULTS
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345'
and (SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE < #createodbcdate(TodaysDate -30)#
or SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE <> LATESTRESULTS.latestdate)
order by id) where rownum <= 2
Any help would be greatly appreciated!!
select * from (select SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from MCA.SPEND_PLAN_HISTORY,
(SELECT MAX(SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE) AS latestdate
FROM MCA.SPEND_PLAN_HISTORY
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345') LATESTRESULTS
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345'
and (SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE < #createodbcdate(TodaysDate -30)#
or SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE <> LATESTRESULTS.latestdate)
order by id) where rownum <= 2
SQLMicrosoft SQL ServerOracle Database
Last Comment
diecasthft01
You should remove "order by id" because it is in the internal query.
If you need ORDER BY move it to the end:
) where rownum <= 2
order by id
rownum <= 2 shoaled be changed to SELECT TOP (2) in MS SQL Server as Kent suggested.
If there is no column rownum in your query you can generate it:
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id ASC) AS RowNum,
...other cols...
FROM ...
) t
WHERE t.RowNum <= 2
If you need ORDER BY move it to the end:
) where rownum <= 2
order by id
rownum <= 2 shoaled be changed to SELECT TOP (2) in MS SQL Server as Kent suggested.
If there is no column rownum in your query you can generate it:
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id ASC) AS RowNum,
...other cols...
FROM ...
) t
WHERE t.RowNum <= 2
ASKER
So then, I don't need the rownum <= 2 if I use the SELECT TOP (2) in the query? I don't have a column called rownum in my table.
Hi,
rownum is a pseudo column that assigns a number to each row returned by a query.
https://www.interviewsansar.com/rowid-vs-rownum-vs-row_number-in-oracle/
https://blog.dbi-services.com/oracle-rownum-vs-rownumber-and-12c-fetch-first/
https://www.oracletutorial.com/oracle-analytic-functions/oracle-row_number/
So your query would be translated from Oracle SQL syntax
Regards,
Tomas Helgi
rownum is a pseudo column that assigns a number to each row returned by a query.
https://www.interviewsansar.com/rowid-vs-rownum-vs-row_number-in-oracle/
https://blog.dbi-services.com/oracle-rownum-vs-rownumber-and-12c-fetch-first/
https://www.oracletutorial.com/oracle-analytic-functions/oracle-row_number/
So your query would be translated from Oracle SQL syntax
select * from (select SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from MCA.SPEND_PLAN_HISTORY,
(SELECT MAX(SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE) AS latestdate
FROM MCA.SPEND_PLAN_HISTORY
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345') LATESTRESULTS
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345'
and (SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE < #createodbcdate(TodaysDate -30)#
or SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE <> LATESTRESULTS.latestdate)
order by id) where rownum <= 2
select TOP 2 * from (select SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from MCA.SPEND_PLAN_HISTORY,
(SELECT MAX(SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE) AS latestdate
FROM MCA.SPEND_PLAN_HISTORY
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345') LATESTRESULTS
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345'
and (SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE < #createodbcdate(TodaysDate -30)#
or SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE <> LATESTRESULTS.latestdate)
order by id)
Regards,
Tomas Helgi
ASKER
I can see the logic behind the SQL query, but it still gives me the same error I started with.....
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I tried just taking out the order by, then get Incorrect syntax near ')'
I also tried to move order by outside the paren, and get "Incorrect syntax near the keyword 'order'."
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I tried just taking out the order by, then get Incorrect syntax near ')'
I also tried to move order by outside the paren, and get "Incorrect syntax near the keyword 'order'."
ASKER
This query works, but gives me the wrong two records:
select top 2 SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from MCA.SPEND_PLAN_HISTORY,
(SELECT MAX(SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE) AS latestdate
FROM MCA.SPEND_PLAN_HISTORY
WHERE SPEND_PLAN_HISTORY.PROJ_NO = 12345') LATESTRESULTS
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345'
and (SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE < #createodbcdate(TodaysDate -30)#
or SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE <> LATESTRESULTS.latestdate)
It gives me the earliest two dates (id numbers 25 and 26), instead of the latest two dates (id 1316 and 1317)
select top 2 SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from MCA.SPEND_PLAN_HISTORY,
(SELECT MAX(SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE) AS latestdate
FROM MCA.SPEND_PLAN_HISTORY
WHERE SPEND_PLAN_HISTORY.PROJ_NO = 12345') LATESTRESULTS
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345'
and (SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE < #createodbcdate(TodaysDate -30)#
or SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE <> LATESTRESULTS.latestdate)
It gives me the earliest two dates (id numbers 25 and 26), instead of the latest two dates (id 1316 and 1317)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Got it.....I think....I missed the order by in the new query.
Changing the first line to:
select * from (select TOP 1000 SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from
may work for you....