select works in db, but not across jdbc...

[Matt Horton]

I take it this is a driver error?

SELECT dbo.ORDER.ID, CONVERT(CHAR(12), dbo.ORDER.ORDERDATE, 1) AS
ORDERDATE, dbo.USERS.NAME, dbo.USERS.EMAIL, dbo.USERS.PHONE,
dbo.TCOMPLETE.COMPLETE AS STATUS, dbo.ORDER.NOTES FROM dbo.ORDER
INNER JOIN dbo.TCOMPLETE ON dbo.ORDER.COMPLETEID = dbo.TCOMPLETE.ID
LEFT OUTER JOIN dbo.USERS ON dbo.ORDER.CLIENTID = dbo.USERS.ID
WHERE (dbo.ORDER.LABID IN (SELECT dbo.USERLABINFO.LABID FROM dbo.USERS
INNER JOIN dbo.USERLABINFO ON dbo.USERS.ID = dbo.USERLABINFO.USERID
WHERE dbo.USERS.ID = 10))

This works great in SQL Server... but across jdbc, it throws the error:

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'DBO.ORDER.ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:334)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:249)
at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcResultSet.java:6063)
at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet.java:150)
at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:420)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:250)
-on and on...

Is there a way to get jdbc to handle IN (SOME SELECT STATEMENT)? This seems to be a perpetual problem for me, and I have to come up w/ hokey sql workarounds to avoid the problem (oftentimes the exception is only thrown when there is no data that fulfills the query).
edit: silly end tags.
[ August 25, 2003: Message edited by: Matt Horton ]

[Dana Hanna]

Its that wacky ODBC driver that you are using. You are using "sun.jdbc.odbc.JdbcOdbcDriver" for your Jdbc Driver (and jdbc dbc:<db_name> for your url...). Use the Microsoft SQL Server jdbc driver.
The sun odbc driver doesn't conform to a lot of stuff... Example - the executeQuery() method throws an exception when no rows are returned, even though it should return an empty resultset.
The microsoft driver is available for free on thier site, and is a high performance JDBC Type-4 driver.
[ August 25, 2003: Message edited by: Dana Hanna ]

[Matt Horton]

Thanks Dana, you are my hero!