Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

use of NOT IN leads to an invalid column name error

 
usher beck
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!