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

Are you sure you get that error from those exact queries? The first should get ORA-00936 because you're not using an alias; the second should be OK (replacing reserved word 'DATE' with your actual column name), though without a subquery it won't do quite what you want anyway. — Alex Poole, Jun 10 '15 at 14:15
So are you double-quoting the ROWNUM, or is something doing that automatically for you? If so can you tell what is doing it? — Alex Poole, Jun 10 '15 at 15:53
I believe that ODAC is doing it for me. I believe that that is supposed to differentiate it from a field name or value ... so that it is recognized as a special command. — Gary O. Stenstrom, Jun 10 '15 at 18:12
Maybe showing how you're creating and executing the query would help identify a solution - maybe a simple program that demonstrates the problem? Or at least that goes from your original query to where you can see that the quotes have been added, though there maybe be something relevant in the connection set-up. I don't use ODAC but hopefully someone will know what's happening - good luck. — Alex Poole, Jun 10 '15 at 18:19

Maheswaran Ravisankar · Accepted Answer · 2015-06-11T05:10:10.653

6

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.

edited Jun 11 '15 at 05:10

answered Jun 10 '15 at 14:22

Maheswaran Ravisankar

17,652
6
47
69

Hmm, good call, maybe ODAC is adding the double quotes? I'm sure I've seen something do that before but I can't find it right now, and don't have ODAC to play with. Perhaps there's an option to turn automatic quoting off. – Alex Poole Jun 10 '15 at 14:35
Indeed ODAC is adding the double-quotes ... but it should still work, right? – Gary O. Stenstrom Jun 10 '15 at 20:19
@GaryO.Stenstrom , `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.. – Maheswaran Ravisankar Jun 11 '15 at 05:08
I wanted to thank everyone for their responses. I am marking this as the answer because it identified the problem as being the ODAC driver. I installed SQL Developer and everything worked as expected. I think that the problem was that the ODAC Driver was massaging the SQL inappropriately. – Gary O. Stenstrom Jun 20 '15 at 16:51

score 3 · Answer 2 · answered Jun 10 '15 at 14:14

3

Can try this approach:

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

answered Jun 10 '15 at 14:14

Arulkumar

12,966
14
47
68

score 0 · Answer 3 · answered Jun 10 '15 at 14:04

0

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

answered Jun 10 '15 at 14:04

Mackers

1,039
8
21

score 0 · Answer 4 · answered Jun 10 '15 at 14:04

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

ROWNUM returns as "invalid identifier"

4 Answers4