SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier

Question

Can below scenario of duplicate alias lead to an error when executed from JDBC or hibernate:

SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier

select * From table_master VW
LEFT OUTER JOIN TABLE(test_func(1, 300)) vw 
ON VW.table_key = vw.function_key

Facing this in production only. It works fine in test environment.

ya.. it works as oracle don't complain. But in production not sure why it gives error. Can some oracle schemas be case seneitive. — sam_evang
I agree that I can get the query to work in Oracle 12c myself (maybe production is on a different version of Oracle?). But in my opinion, that should be the bug. I don't think the query should be considered valid, it's ambiguous. If I were you, I would just assign 2 distinct aliases, which is really the only sane thing to do here, and try again with that. — sstan
What are the exact versions of the two servers (edit your question and add the output of select * from v$version) — a_horse_with_no_name

Davel_AG Davel_AG · Accepted Answer · 2019-08-13T23:24:39

In my case the DB field didn't exist, and it was returning SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier

After Creating the field, of course it worked,

Double check those fields and make sure they are matching...

Hope it may give you a clue to find the issue