10 Replies Latest reply: Jan 10, 2013 9:41 PM by 636309 RSS

    Need text in NULL fields of grouping sets

    636309
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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.