Clever Geek Handbook

ROWNUM is returned as "invalid identifier"

I am running a very simple pick regarding oracle database (not sure about the version).

SELECT * FROM ACCOUNTING WHERE ID = 123456 ORDER BY DATE

I only want to return the most recent entry. So I 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 this should work. Can anyone see what I am missing? Could this be a driver issue? I am using the following package ... (Oracle ODAC 11.20.30 x64)

UPDATE

Thanks for all your responses ... I apologize for any confusion I have created in my efforts to simplify the script. The ODAC driver actually splits the request and formats it for me, so what I originally posted is not an exactly executable request ... here in particular what is spitting out the driver that generates 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 try ...

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


source to share


4 answers


May be used in double quotes in your actual query ROWNUM

. Otherwise, this error is not possible.

Although your first request 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 a function without parameters .. and by the way, "ROWNUM"

makes oracle look for such a column in your table.

Quoted identifiers, when an Oracle reserved keyword, exceeds its original purpose and behaves like a custom column.

Not sure how to stop the query builder to interpret this path. I would have thought it was BUG .

+4


source


You can try this approach:



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


source


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

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


source


ROWNUM is set after returning the result set and is the order in which Oracle fetched a row from the table (s), so you can limit the output to 10 rows by doing something like:

...
FROM mytable
where ROWNUM < 11;

You are not using it to get the most recent entry. It is not a column in a table hence the error you are getting.

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

0


source






More articles:


All Articles