0

I have two databases. 1. Oracle 2. SQL Server

Both have the same table TABLE. I have to fetch data in limits, order by some columns, in both the table and validate it (against each other). Suppose the limit is 100 to 150

For SQL I am using 

select * 
from (
    select row_number() over (order by specific_column) as row, *  
    from  Table
) as t
where row between 100 and 150

For Oracle I am using

select * 
from (
      select columnname, rownum r 
      from Table
 ) a where a.r>=100 and a.r<=150 order by specific_column

But the results I am getting is entirely different in each output.

May be the orderby is working first in sqlserver and then its fetching it by rownum, and vice versa in oracle.

PS: Please note that, the version of oracle what I am using is 7.3.x and order by in subquery is not supported.

Abhii
  • 255
  • 2
  • 4
  • 17
  • 1
    I think you can use the first query for both databases. (http://www.oracle-developer.net/display.php?id=102) – Zohar Peled May 25 '15 at 11:37
  • No, its not working, Inavlid Function for row_number(). May be because of the older version of oracle – Abhii May 25 '15 at 11:41
  • In oracle when you call select columnname, rownum r from Table you don't order by specific_column. Shouldn't you use ordering there as well? – Etibar - a tea bar May 25 '15 at 11:43
  • @EtibarHəsənov : Order by is not working there, I guess it doesn't work in subqueries. Missing right parenthesis error is what its coming if I am using order by in subquery – Abhii May 25 '15 at 11:48
  • @Justin Cave : This question is not duplicate. There is only a query in oracle, but I want an equivalent query in sql server in particular order by. Please provide the solution and untag it from duplicate – Abhii May 25 '15 at 12:01
  • @Abhii - Your SQL Server query appears to be functionally correct (assuming a recent enough version of SQL Server). I can't mark a question as a duplicate of multiple other questions-- there are plenty of SQL Server pagination questions on StackOverflow that takl about this. – Justin Cave May 25 '15 at 12:08
  • @JustinCave - I agree that there are many answers in portal regarding SQL pagination. But my problem is pagination in SQL and Oracle are retrieving different result set and I am expecting answer for that. If you can untag from duplicate, i may get more answers – Abhii May 25 '15 at 12:37
  • @Abhii - There is no way to untag a duplicate. I believe the problem is that your Oracle query is functionally incorrect. That's why I closed it as a duplicate of the Oracle question that has a correct version of a pagination query. – Justin Cave May 25 '15 at 12:38
  • @JustinCave I have tried that solution, Error : ORA:00907 Missing Right Parenthesis, this error is coming, anytime I am using order by in subquery. Please consider, oracle 8.x it is. If I am removing order by clause from subquery, then its working fine. But I need to use it with order by clause. – Abhii May 25 '15 at 12:50
  • Exactly what version of Oracle are you using? 8.x could mean anything from 8.0.3 to 8.1.7. That's a huge number of releases. Of course, all of them have been desupported for at least the better part of a decade. If you are using 8.0.x, you probably can't do this in a single SQL statement https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064#12379480725590 – Justin Cave May 25 '15 at 12:53
  • @JustinCave : Its 8i I guess or ora81 – Abhii May 25 '15 at 12:55
  • If you are using 8i, then you're using 8.1.x. If you are using 8.1.x, the `rownum` pagination query in the duplicate thread will work. But don't guess, get the actual version. Query `v$version` if you don't know. – Justin Cave May 25 '15 at 12:57
  • @JustinCave : Sir its oracle 7 server release 7.3.4.4.1 - Production – Abhii May 25 '15 at 13:03
  • Then you're out of luck. You can't do it in a SQL statement. You'd need procedural logic. Oracle 7 has been desupported for almost 20 years. – Justin Cave May 25 '15 at 13:05
  • @JustinCave Can you please provide me that PL. – Abhii May 25 '15 at 13:10
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78692/discussion-between-justin-cave-and-abhii). – Justin Cave May 25 '15 at 13:11

0 Answers0