Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Angry Unanswered: Error 8120 when selecting MAX(...)

    Hi Folks,

    I have a very strange problem with the MSSQL JDBC drivers (SP1).
    I am calling executeQuery() on a very simple query:

    SELECT MAX(fulltext_id) FROM contractor

    I am getting an error 8120 (column is not contained in an aggregate-- see stack trace below). It thinks that 'id' is part of the column list, yet it is not.

    Now, the strange thing is, when I run SQL Profiler, I see this line:

    exec sp_cursoropen @P1 output, N'SELECT MAX(fulltext_id) , * FROM contractor', @P2 output, @P3 output, @P4 output

    It seems that someone (the driver?) added a comma and a "*" after the MAX(fulltext_id) clause! What gives?

    Thanks much,
    Steve

    ===

    java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column 'contractor.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
    at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
    at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
    at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
    at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
    at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
    at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
    at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown Source)
    at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown Source)
    at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
    at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown Source)
    at com.microsoft.jdbc.base.BaseStatement.executeQuery (Unknown Source)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

    blindman

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    Originally posted by blindman
    I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

    blindman
    Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

    I am still kinda confused, but opening the cursor as read-only fixed the problem... :?

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by sehugg
    Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

    I am still kinda confused, but opening the cursor as read-only fixed the problem... :?
    Obviously a bug in the JDBC driver.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •