Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: ORA-00937: not a single-group... BUT, not using any aggregate functions

    After reading a ton of posts where people were using an aggregate function without a GROUP BY clause I figured I'd post my question. I'm not using any aggregate functions, so I don't understand why I'm getting this error. I am going through an Intersolv ODBC driver v 3.11 and writing in java. I wonder if it's something to do with the ODBC driver maybe thinking something I'm using is an aggregate function. My SQL:

    SELECT ADDRESS_ID, ADDRESS_NUM, NVL(ADDRESS_FRAC, ' '),
    NVL(PRE_DIRECTION, ' '), ROAD_NAME_30,
    NVL(ROAD_SFX_VT.DESCRIPTION, ' '),
    NVL(POST_DIRECTION, ' '), NVL(ADDRESS_UNIT, ' ')
    FROM SITE.ADDRESS, SITE.ROAD_NAME, SITE.ROAD_SFX_VT
    WHERE ADDRESS.ROAD_NAME_ID = ROAD_NAME.ROAD_NAME_ID AND
    ROAD_NAME.ROAD_SFX_ID = ROAD_SFX_VT.ROAD_SFX_ID(+) AND
    ADDRESS.ADDRESS_NUM LIKE '6%' AND
    (ROAD_NAME_30 LIKE 'NORTH CRESEN%' OR
    (PRE_DIRECTION = 'N' AND ROAD_NAME_30 LIKE 'CRESEN%'))
    ORDER BY ROAD_NAME_30, ADDRESS_NUM

    Thanks.
    Steve

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    i think it's the driver or possibly outdated drivers that still exist
    on the system conflicting with your current driver.

    What I would do to debug this would be to trace the session
    that the app is running and then TKPROF the tracefile. This can then
    tell you if the driver is somehow re-writing your code into some kind
    of aggregate function (this has been known to happen with MS ODBC
    drivers).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Have you confirmed that this error does NOT occur when you run the exact same SQL statement through SQL Plus?

  4. #4
    Join Date
    Sep 2004
    Posts
    2
    Duck Man:
    You nailed it - that was exactly the problem. When I looked at the SQL that actually got sent to the server, it had COUNT(*) as the first thing being returned from the select statement. Any idea how or why it would do this - it seems crazy? I did notice if the SQL I sent had wildcards in the WHERE for both my Address_Number and Road_Name columns I got this behavior. If I only used a wildcard for the Address_Num, it didn't generate the COUNT(*). Any ideas on getting around it besides trying a newer / different ODBC driver?
    Thanks.
    Steve

    Quote Originally Posted by The_Duck
    i think it's the driver or possibly outdated drivers that still exist
    on the system conflicting with your current driver.

    What I would do to debug this would be to trace the session
    that the app is running and then TKPROF the tracefile. This can then
    tell you if the driver is somehow re-writing your code into some kind
    of aggregate function (this has been known to happen with MS ODBC
    drivers).

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    wow, that was a shot in the dark!

    I would suggest POSSIBLY removing all drivers and re-installing the
    most recent.

    Does that sound like a good plan?
    I am somewhat out of my element here.

    Otherwise you could try Google-ing the specific problem or look up
    some BBoards regarding your driver?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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