*
The moose likes Oracle/OAS and the fly likes Why does this give invalid column name, is there an alternate syntax for NOT IN? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Why does this give invalid column name, is there an alternate syntax for NOT IN?" Watch "Why does this give invalid column name, is there an alternate syntax for NOT IN?" New topic
Author

Why does this give invalid column name, is there an alternate syntax for NOT IN?

eileen keeney
Ranch Hand

Joined: May 04, 2009
Posts: 51
For some reason, java chokes when NOT IN is used in a db query.

I took the query down to a very simplified query to test this.

Initially I had something like this:
select x1, x2 from tableX where x1 NOT IN (select Y1 from table Y);

To try to determine what would work I played with code, and took it down to something very simple to see if a simple NOT IN would work.
So I tried this:
select x1, x2 from tableX where x1 not in ('value1', 'value2')
I got the same invalid column name error.


This does NOT give an error

select x1, x2 from tableX where x1 IN ('value1', 'value2')

This DOES give an invalid column name error
select x1, x2 from tableX where x1 NOT IN ('value1', 'value2')

All of the queries work fine when given to sql plus directly.

Can any one get a query to work, passed as an Oracle Statement, to java, with a NOT IN in the query?
Is there an alternate syntax I can use in Java for a NOT IN query?

I really do not want to go through each record 1x1, as I have been doing.
I am trying to speed up what I had previously coded.

Thank you
John Bengler
Ranch Hand

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

no, there is no different syntax for "not in".

The behaviour you've described is very strange, indeed...


Can you provide the exact error message? Maybe it contains a hint?


John
eileen keeney
Ranch Hand

Joined: May 04, 2009
Posts: 51
not much there:

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)=


Removing the NOT, gives a query that works, at least as far as not returning an error.

The column does have an index (not a unique index, but it has an index).
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
This is really strange...

I'm afraid I don't know a soultion for this right now...

How about a workaround:

If I understood your original post you want to exclude all rows where a value exists in another table..



Of course this is a real ugly workaround...


Anyone a better idea?
eileen keeney
Ranch Hand

Joined: May 04, 2009
Posts: 51
John Bengler wrote:This is really strange...

I'm afraid I don't know a soultion for this right now...

How about a workaround:

If I understood your original post you want to exclude all rows where a value exists in another table..



Of course this is a real ugly workaround...


Anyone a better idea?


Yes, that is what I am trying to do.
And your idea works.
Ugly and working, is better then elegant and not working.
And I believe it is still faster then going through one by one and comparing.

Thanks.
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
You're welcome.

If you have indexes on X1 and Y1 the workaround might even be faster as the NOT IN solution, because for a not equals criterion no indexes can be used. This may depend also on the structure of your data, but I often used such minus constructs for performance tuning.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Why does this give invalid column name, is there an alternate syntax for NOT IN?