July 14, 2014 at 6:10 pm
I am writing a large select statement in MS SQL to select data from linked Oracle 11g server.
Started with this
e.g.
Select top 100 * from
table1
left outer join table2 on table1.id = table2.id
left outer join table3 on table1.id = table3.id
I get the following error
OraOLEDB.Oracle" for linked server "BIS" returned message "ORA-01403: no data found
if i remove one table or if i remove the "top 100" clause it works.
since i am building a rather large select with many left outer joins i'd like to limit the data i retrieve.
Does anyone know what is causing the top 100 to behave like this and what the workaround is ?
July 21, 2014 at 7:55 am
Oracle code uses: "rownum < 100". It does not use TOP command.
Ex: Select * from 'table1' where rownum < 100;
July 21, 2014 at 9:02 am
I'm also pretty sure that, even across a linked server, Oracle defaults to being case-sensitive for all things like table names, column names, etc, ad infinitum (just to cover the next possible error that I see coming up).
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2014 at 5:37 pm
I'm calling the data through linked tables from SQL2008.
The method 'rownum' is not a SQL term.
If you try to use it in a select statement of data on SQL2008 to retrieve data from Oracle you get this error
Msg 207, Level 16, State 1, Line 3
Invalid column name 'rownum'.
Top n will work on one table
If i do this
select top n 3 from table1
I get 3 rows.
It's only when i start to join the tables and then do a top n that Oracle or the intermediare between SQL and Oracle starts to produce errors
July 24, 2014 at 5:39 pm
it's not a syntax or case error.
I dont' change the top select part of the statement.
I simply comment out the last table and the statement works.
As soon as i include more than two tables in a join and use a top n , Oracle returns an error.
July 24, 2014 at 6:46 pm
edit: Never mind, reread the post and saw that if the TOP 100 was removed the query ran.
July 25, 2014 at 9:47 am
Okay, I still have to ask. Are you sure there is data in all three tables?
July 27, 2014 at 4:52 am
Yes.
If i replace the top n with select * and leave the rest of the script the same I get a lot of records back.
The error exist only if i have more than a few tables in the join and use top n
Take top n out and i can join on as many tables as i want.
July 27, 2014 at 4:58 am
Are all the tables used in the Join located at the Orcle server?
If so, I recommend to change it to a remote Query (at least for all tables located at the Oracle server).
In that remote query yo should use ROWNUM<100 again, since it'll be executed using Oracle syntax.
July 28, 2014 at 12:47 am
Have you tried this?
Select top 100 * from (
select * from table1
left outer join table2 on table1.id = table2.id
left outer join table3 on table1.id = table3.id
)
Also be notified that rownum < 100 returns 99 rows only. Use rownum <= 100.
July 29, 2014 at 9:07 am
hi, I had similar problem a few years back... if im not mistaken, it all depended on the driver used to set up the linked server.
if used Microsoft driver (ODBC) then the top x works fine and if the driver was Oracle's then must use the rownum clause....
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy