ROWNUM returns as "invalid identifier"

Question

I am running a very basic select against an oracle database (not sure of the version).

SELECT * FROM ACCOUNTING WHERE ID = 123456 ORDER BY DATE

I want to return only the most recent record. So I have tried ...

SELECT ROWNUM, * FROM ACCOUNTING WHERE ID = 123456 ORDER BY DATE
SELECT * FROM ACCOUNTING WHERE ID = 123456 AND ROWNUM < 2 ORDER BY DATE

I get the same result every time ...

Error Source: System.Data.OracleClient    
Error Message: ORA-00904: "ROWNUM" : invalid identifier

Everything i see and read suggests that this should work. Can anyone see what I am missing? Could this be an issue with the driver? I am using the following package ... (Oracle ODAC 11.20.30 x64)

UPDATE

Thank you for all your replies ... I apologize for any confusion I created in my efforts to simplify the scenario. The ODAC driver is actually breaking the query out and formatting it for me, so what I originally posted is not exactly the query being run ... here's, specifically what the driver is spitting out that is generating the error ...

SELECT "ROWNUM", ID, SITE_ID, READING_DATE, SUBMITTED_DATE, DEPOSIT_DATE
FROM    ACCOUNTING
WHERE   (SITE_ID = 33730)
ORDER BY READING_DATE

And for my second attempt ...

SELECT ID, SITE_ID, READING_DATE, SUBMITTED_DATE, DEPOSIT_DATE
FROM    ACCOUNTING
WHERE   (SITE_ID = 33730) AND ("ROWNUM" < 2)
ORDER BY READING_DATE

Answer 1

Your actual query might be using ROWNUM within double quotes. Otherwise, this error is not possible.

Though your first query would be ORA-00936: missing expression

select * from dual WHERE "ROWNUM" < =3;

Error report -
SQL Error: ORA-00904: "ROWNUM": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

ROWNUM is a pseudo-column and it is like function without parameters.. and by the way "ROWNUM" makes oracle to search for such a column in your table..

Quoted identifiers when is a Oracle reserved keyword, would surpass its original purpose, and behaves as user defined column.

Unsure, of how to stop the Query builder to interpret this way. I would consider this a BUG.

Answer 2

I believe when you select comma-delimited columns that includes the *, you need to alias the table.

SELECT A.ROWNUM, A.* FROM ACCOUNTING A WHERE ID = 123456 ORDER BY DATE

Answer 3

ROWNUM is set after the resultset is returned, and is the order in which Oracle selected the row from the table(s), so you can restrict the output to 10 rows by doing something like:

...
FROM mytable
where ROWNUM < 11;

You don't use it for getting the most recent record. It's not a column in the table, hence the error you received.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns009.htm#SQLRF00255

Answer 4

Can try this approach:

SELECT * FROM 
   (SELECT ROWNUM R, * FROM ACCOUNTING WHERE ID = 123456 ORDER BY DATE
) WHERE R < 2;