Link to home
Start Free TrialLog in
Avatar of diecasthft01
diecasthft01

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
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

How many rows do you expect in the inner 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....
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
Avatar of diecasthft01
diecasthft01

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
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                         

Open in new window

to SQL Server syntax
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)                        

Open in new window


Regards,
    Tomas Helgi
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'."
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)
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Got it.....I think....I missed the order by in the new query.