jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes use of NOT IN leads to an invalid column name error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "use of NOT IN leads to an invalid column name error" Watch "use of NOT IN leads to an invalid column name error" New topic
Author

use of NOT IN leads to an invalid column name error

usher beck
Greenhorn

Joined: Jul 21, 2009
Posts: 5
The query works just fine when placed directly into sql plus

The query works, called from a Java using an Oracle Statement, ONLY IF I remove the NOT.
However, with NOT it chokes, and gives me this:

selQ=select lenum, zeugn from stock_rpt where lenum NOT in ('X12345', 'X23456')
Could Select or Read Selected data
java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3319)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1926)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1515)
at ecspack.StockRpt.selRecsByQ(StockRpt.java:172)
at ecspack.StockRpt.selByDiff(StockRpt.java:261)
at teste.main(teste.java:40)

The column does have an index.

The above is my simplified test query, I am using to narrow down the problem.
SelQ is the value of the Select String.
Really I want to do something like this:

select x1, x2 from tableX where x1 NOT IN (select y1 from tableY)

I know I can just compare records one at a time, but I would rather not, if there is a way to make NOT IN work.

Thank you
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Usher,

There was a thread about this two days ago:

invalid column name when using NOT IN

Unfortunately it didn't bring up a solution, just a workaround. At least you now know you're not the only one facing that problem..

John
usher beck
Greenhorn

Joined: Jul 21, 2009
Posts: 5
John Bengler wrote:Hi Usher,

There was a thread about this two days ago:

invalid column name when using NOT IN

Unfortunately it didn't bring up a solution, just a workaround. At least you now know you're not the only one facing that problem..

John


ThankYou Sir.
 
Consider Paul's rocket mass heater.
 
subject: use of NOT IN leads to an invalid column name error