Results 1 to 5 of 5

Thread: Help on rollup

  1. #1
    Join Date
    Jan 2006
    Posts
    9

    Unanswered: Help on rollup

    Hi

    I need the equivalent of the following query in oracle without the rollup construct which gives the same result set as using the rollup.

    SELECT
    DISTINCT
    decode(a1.a, null, 0, 1) dec1 ,
    a1.a, a1.b,a1.c from test_groupby a1
    GROUP BY rollup(a1.a, a1.b, a1.c)


    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this?
    Code:
    SELECT dec1, a, b, c FROM (
      SELECT DECODE(a, NULL, 0, 1) dec1, a, b, c FROM test_groupby GROUP BY a, b, c
        UNION
      SELECT DECODE(a, NULL, 0, 1) dec1, a, b, NULL FROM test_groupby GROUP BY a, b, NULL
        UNION
      SELECT DECODE(a, NULL, 0, 1) dec1, a, NULL, NULL FROM test_groupby GROUP BY a, NULL, NULL
        UNION
      SELECT DECODE(a, NULL, 0, 1) dec1, NULL, NULL, NULL FROM test_groupby
    )
    ORDER BY a, b, c;

  3. #3
    Join Date
    Jan 2006
    Posts
    9
    Hi Littlefoot

    Thanks for the equivalent query .

  4. #4
    Join Date
    Jan 2006
    Posts
    9
    Hi

    I applied the same method for the following query

    SELECT DISTINCT (decode(a1.a, null, 0, 1) + decode(a1.b, null, 0, 1) )dec1 ,
    a1.a, a1.b,a1.c from test_groupby a1
    GROUP BY rollup(a1.a, a1.b, a1.c)

    The below query without rollup throws the error Error: ORA-00979: not a GROUP BY expression

    SELECT dec1, a, b, c FROM (
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, c FROM test_groupby GROUP BY a, b, c
    UNION
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, NULL FROM test_groupby GROUP BY a, b, NULL
    UNION
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, NULL, NULL FROM test_groupby GROUP BY a, NULL, NULL
    UNION
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, NULL, NULL, NULL FROM test_groupby
    )
    ORDER BY a, b, c

    So, I added the column b in the group by statement of

    SELECT dec1, a, b, c FROM (
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, c FROM test_groupby GROUP BY a, b, c
    UNION
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, NULL FROM test_groupby GROUP BY a, b, NULL
    UNION
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, NULL, NULL FROM test_groupby GROUP BY a,b, NULL, NULL
    UNION
    SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, NULL, NULL, NULL FROM test_groupby
    )ORDER BY a, b, c

    but the result set of the query is not the same as the query with the rollup construct.

    Please explain what is wrong with the query and why the result set varies?.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You should have added

    (DECODE(a, NULL, 0, 1) + DECODE(b, NULL, 0, 1))

    into the GROUP BY clause instead of only "b".

    Why are results different? You mean, a new query has value of 2 in the "dec1" column, while the old query has value 1 in there?

    Old: DECODE(a, NULL, 0, 1)
    New: (DECODE(a, NULL, 0, 1) + DECODE(b, NULL, 0, 1))

    See the difference?

Posting Permissions

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