Error when selecting top N records from oracle

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

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

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

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

  • 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

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

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

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

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

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

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

  • 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