This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android 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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: use of NOT IN leads to an invalid column name error
 
Similar Threads
Error in named native query (Invalid Column Name)
Why does this give invalid column name, is there an alternate syntax for NOT IN?
SQLException Exhausted resultset
java.sql.SQLException: Invalid column index
Invalid Column Name in getString()--query worked