use of NOT IN leads to an invalid column name error

[usher beck]

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]

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]

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.