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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply