Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: Problems with a GROUP BY inside a SUBQUERY, using collections in Oracle 9i

    Problems with a GROUP BY inside a SUBQUERY, using collections in Oracle 9i

    Hi.

    I'm working PL/SQL with Oracle 9i and I have a query like this

    SELECT ...
    FROM (SELECT A
    FROM TABLE(CAST(myTable1 AS TABLE1))
    GROUP BY A,B,C
    ) X,
    TABLEA,
    TABLEB
    WHERE ...

    myTable1 is an instance of a collection named TABLE1, the external select has other tables, some collections and some SQL tables.

    I've tested several combinations of this query: using or not using collections, using or not using GROUP BY, and the conclusion is that if I have a GROUP BY inside a subquery on a collection, it gives the error ORA-00979: NOT A GROUP BY EXPRESSION

    Has anyone received some like this?

    I'd appreciate any advice.

  2. #2
    Join Date
    Jun 2003
    Posts
    294
    Tha query works ok, in oracle 8i, but I doesn't work in Oracle 9i

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    how about grouping when you create your collection instead
    of grouping your collection after the fact?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jun 2003
    Posts
    294
    Cause we use the collection, grouped by different caracteristics, in different places of the code

Posting Permissions

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