Error when selecting top N records from oracle

Question

Post reply

Error when selecting top N records from oracle

ErickTreeTops

Ten Centuries

Points: 1253
More actions
July 14, 2014 at 6:10 pm

#287905

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 ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Michael_Garrison Hall of Fame Points: 3110 More actions · Answer 1

Oracle code uses: "rownum < 100". It does not use TOP command.

Ex: Select * from 'table1' where rownum < 100;

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ErickTreeTops Ten Centuries Points: 1253 More actions · Answer 3

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

ErickTreeTops Ten Centuries Points: 1253 More actions · Answer 4

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

edit: Never mind, reread the post and saw that if the TOP 100 was removed the query ran.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Okay, I still have to ask. Are you sure there is data in all three tables?

ErickTreeTops Ten Centuries Points: 1253 More actions · Answer 7

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.

LutzM SSC Guru Points: 107049 More actions · Answer 8

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ivan.peter SSC Enthusiast Points: 181 More actions · Answer 9

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.

hugo sesoldi SSC Journeyman Points: 92 More actions · Answer 10

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