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 ]