This discussion is archived
10 Replies Latest reply: Jan 10, 2013 7:41 PM by 636309 RSS

Need text in NULL fields of grouping sets

636309 Newbie
Currently Being Moderated
Hi,
I'm using Oracle 11g (sorry don't have the release at this time). I have the following dataset, query and results.

DROP TABLE dimension_tab;
CREATE TABLE dimension_tab (
fact_1_id NUMBER NOT NULL,
fact_2_id NUMBER NOT NULL,
fact_3_id NUMBER NOT NULL,
fact_4_id NUMBER NOT NULL,
sales_value NUMBER(10,2) NOT NULL
);

INSERT INTO dimension_tab
SELECT TRUNC(DBMS_RANDOM.value(low => 1, high => 3)) AS fact_1_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 6)) AS fact_2_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_3_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_4_id,
ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
FROM dual
CONNECT BY level <= 1000;
COMMIT;
SELECT fact_1_id,
       fact_2_id,
       fact_3_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id, fact_3_id) AS grouping_id
FROM   dimension_tab
GROUP BY GROUPING SETS((fact_1_id, fact_2_id), (fact_1_id, fact_3_id))
ORDER BY fact_1_id, fact_2_id, fact_3_id;

FACT_1_ID  FACT_2_ID  FACT_3_ID SALES_VALUE GROUPING_ID
---------- ---------- ---------- ----------- -----------
         1          1                4363.55           1
         1          2                4794.76           1
         1          3                4718.25           1
         1          4                5387.45           1
         1          5                5027.34           1
         1                     1      2737.4           2
         1                     2     1854.29           2
         1                     3     2090.96           2
         1                     4     2605.17           2
         1                     5     2590.93           2
         1                     6      2506.9           2
         1                     7     1839.85           2
         1                     8     2953.04           2
         1                     9     2778.75           2
         1                    10     2334.06           2
         2          1                5652.84           1
         2          2                4583.02           1
         2          3                5555.77           1
         2          4                5936.67           1
         2          5                4508.74           1
         2                     1     3512.69           2
         2                     2     2847.94           2
         2                     3      2972.5           2
         2                     4     2534.06           2
         2                     5     3115.99           2
         2                     6     2775.85           2
         2                     7     2208.19           2
         2                     8     2358.55           2
         2                     9     1884.11           2
         2                    10     2027.16           2
I would like to dispaly the word 'totals' in the NULL fields of FACT_2_ID and FACT_3_ID. However, I don't want to use the NVL function since some of my real data can include NULL values as part of the groupings. Any suggestions?
  • 1. Re: Need text in NULL fields of grouping sets
    jeneesh Guru
    Currently Being Moderated
    You could DECODE the output of GROUPING function..
    select fact_1_id,
           decode(grouping(fact_2_id),1,'Total',to_char(fact_2_id)) fact_2_id,
           decode(GROUPING(fact_3_id),1,'Total',to_char(fact_3_id)) fact_3_id,
           SUM(sales_value) AS sales_value,
           grouping_id(fact_1_id, fact_2_id, fact_3_id) as grouping_id     
    FROM   dimension_tab
    GROUP BY GROUPING SETS((fact_1_id, fact_2_id), (fact_1_id, fact_3_id))
    ORDER BY fact_1_id, fact_2_id, fact_3_id;
  • 2. Re: Need text in NULL fields of grouping sets
    jeneesh Guru
    Currently Being Moderated
    Between, your columns are defined as  NOT NULL

    How can you have NULL value in it..?

    Edited by: jeneesh on Jan 10, 2013 10:21 AM
    Ok, you are practicing.....Cool..Go ahead..
  • 3. Re: Need text in NULL fields of grouping sets
    983451 Newbie
    Currently Being Moderated
    that was exactly my question.. (having not null in table definition)
    And the whole thing was dumped from the following location.

    :)

    http://www.oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php

    Cheers,
    Manik.
  • 4. Re: Need text in NULL fields of grouping sets
    jeneesh Guru
    Currently Being Moderated
    Since you are practicing, Adding below the test results with NULL values in FACT_2_ID column..
    alter table dimension_tab  modify(fact_2_id number null);
    
    table DIMENSION_TAB altered.
    
    INSERT INTO dimension_tab
    select trunc(dbms_random.value(low => 1, high => 3)) as fact_1_id,
    null AS fact_2_id,
    TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_3_id,
    TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_4_id,
    ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
    FROM dual
    connect by level <= 5;
    
    5 rows inserted.
    
    select fact_1_id,
           decode(grouping(fact_2_id),1,'Total',to_char(fact_2_id)) fact_2_id,
           decode(GROUPING(fact_3_id),1,'Total',to_char(fact_3_id)) fact_3_id,
           SUM(sales_value) AS sales_value,
           grouping_id(fact_1_id, fact_2_id, fact_3_id) as grouping_id     
    FROM   dimension_tab
    GROUP BY GROUPING SETS((fact_1_id, fact_2_id), (fact_1_id, fact_3_id))
    ORDER BY fact_1_id, fact_2_id, fact_3_id;
    
    ACT_1_ID FACT_2_ID                                FACT_3_ID                                SALES_VALUE GROUPING_ID
    --------- ---------------------------------------- ---------------------------------------- ----------- -----------
            1 1                                        Total                                        5705.18           1 
            1 2                                        Total                                        4824.84           1 
            1 3                                        Total                                        3972.14           1 
            1 4                                        Total                                        5288.34           1 
            1 5                                        Total                                        4974.79           1 
            1 Total                                    1                                             2180.3           2 
            1 Total                                    10                                           2072.58           2 
            1 Total                                    2                                            2439.91           2 
            1 Total                                    3                                             2151.9           2 
            1 Total                                    4                                            2440.66           2 
            1 Total                                    5                                            2560.76           2 
            1 Total                                    6                                            2689.65           2 
            1 Total                                    7                                            3007.05           2 
            1 Total                                    8                                            2705.67           2 
            1 Total                                    9                                            2689.37           2 
            1           "Null Here"                    Total                                         172.56           1 
            2 1                                        Total                                        4863.91           1 
            2 2                                        Total                                        5033.84           1 
            2 3                                        Total                                        5322.72           1 
            2 4                                        Total                                        5271.22           1 
            2 5                                        Total                                        4787.18           1 
            2 Total                                    1                                            2252.35           2 
            2 Total                                    10                                           2855.83           2 
            2 Total                                    2                                            2876.69           2 
            2 Total                                    3                                            2410.56           2 
            2 Total                                    4                                            2257.27           2 
            2 Total                                    5                                            2438.15           2 
            2 Total                                    6                                            3172.73           2 
            2 Total                                    7                                            2048.83           2 
            2 Total                                    8                                            1713.37           2 
            2 Total                                    9                                            3256.38           2 
            2            "Null Here"                   Total                                           3.29           1 
    
     32 rows selected 
  • 5. Re: Need text in NULL fields of grouping sets
    636309 Newbie
    Currently Being Moderated
    jeneesh wrote:
    Since you are practicing, Adding below the test results with NULL values in FACT_2_ID column..
    alter table dimension_tab  modify(fact_2_id number null);
    
    table DIMENSION_TAB altered.
    
    INSERT INTO dimension_tab
    select trunc(dbms_random.value(low => 1, high => 3)) as fact_1_id,
    null AS fact_2_id,
    TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_3_id,
    TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_4_id,
    ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
    FROM dual
    connect by level <= 5;
    
    5 rows inserted.
    
    select fact_1_id,
    decode(grouping(fact_2_id),1,'Total',to_char(fact_2_id)) fact_2_id,
    decode(GROUPING(fact_3_id),1,'Total',to_char(fact_3_id)) fact_3_id,
    SUM(sales_value) AS sales_value,
    grouping_id(fact_1_id, fact_2_id, fact_3_id) as grouping_id     
    FROM   dimension_tab
    GROUP BY GROUPING SETS((fact_1_id, fact_2_id), (fact_1_id, fact_3_id))
    ORDER BY fact_1_id, fact_2_id, fact_3_id;
    
    ACT_1_ID FACT_2_ID                                FACT_3_ID                                SALES_VALUE GROUPING_ID
    --------- ---------------------------------------- ---------------------------------------- ----------- -----------
    1 1                                        Total                                        5705.18           1 
    1 2                                        Total                                        4824.84           1 
    1 3                                        Total                                        3972.14           1 
    1 4                                        Total                                        5288.34           1 
    1 5                                        Total                                        4974.79           1 
    1 Total                                    1                                             2180.3           2 
    1 Total                                    10                                           2072.58           2 
    1 Total                                    2                                            2439.91           2 
    1 Total                                    3                                             2151.9           2 
    1 Total                                    4                                            2440.66           2 
    1 Total                                    5                                            2560.76           2 
    1 Total                                    6                                            2689.65           2 
    1 Total                                    7                                            3007.05           2 
    1 Total                                    8                                            2705.67           2 
    1 Total                                    9                                            2689.37           2 
    1           "Null Here"                    Total                                         172.56           1 
    2 1                                        Total                                        4863.91           1 
    2 2                                        Total                                        5033.84           1 
    2 3                                        Total                                        5322.72           1 
    2 4                                        Total                                        5271.22           1 
    2 5                                        Total                                        4787.18           1 
    2 Total                                    1                                            2252.35           2 
    2 Total                                    10                                           2855.83           2 
    2 Total                                    2                                            2876.69           2 
    2 Total                                    3                                            2410.56           2 
    2 Total                                    4                                            2257.27           2 
    2 Total                                    5                                            2438.15           2 
    2 Total                                    6                                            3172.73           2 
    2 Total                                    7                                            2048.83           2 
    2 Total                                    8                                            1713.37           2 
    2 Total                                    9                                            3256.38           2 
    2            "Null Here"                   Total                                           3.29           1 
    
    32 rows selected 
    Jeneesh, this worked finely. Yeah I got this example from another source. Mine's a little different with the allowable NULLs.
  • 6. Re: Need text in NULL fields of grouping sets
    636309 Newbie
    Currently Being Moderated
    Manik wrote:
    And the whole thing was dumped from the following location.
    What's your point? What difference does it make?
  • 7. Re: Need text in NULL fields of grouping sets
    Manik Expert
    Currently Being Moderated
    No offense buddy.. I was just trying to say that the scripts were taken from that website.. nothing serious.... take it easy. :)

    Cheers,
    Manik.
  • 8. Re: Need text in NULL fields of grouping sets
    636309 Newbie
    Currently Being Moderated
    904760 wrote:
    No offense buddy.. I was just trying to say that the scripts were taken from that website.. nothing serious.... take it easy. :)
    Non taken, pal.

    But, why are you sounding so defensive? :)
  • 9. Re: Need text in NULL fields of grouping sets
    Manik Expert
    Currently Being Moderated
    Just wanted to make sure this is a friendly forum of oracle :)
    BTW you marked the question as answered, so finally you made changes to the DDL of table is it?

    Cheers,
    Manik.
  • 10. Re: Need text in NULL fields of grouping sets
    636309 Newbie
    Currently Being Moderated
    904760 wrote:
    Just wanted to make sure this is a friendly forum of oracle :)
    BTW you marked the question as answered, so finally you made changes to the DDL of table is it?
    Yes, changes were made to the DDL and the results were obtained as desired.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points