OraFAQ Forum: SQL & PL/SQL » Is there a way to do a rollup on only one dimension

Home » SQL & PL/SQL » SQL & PL/SQL » Is there a way to do a rollup on only one dimension (11.2.0.4, Windows 2012 R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there a way to do a rollup on only one dimension [message #666755]

Thu, 23 November 2017 23:04

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

I want to do a snapshot of file#, name, bytes/1024/1024 from v$datafile;

currently I'm using


COLUMN file_num_name_date FORMAT A100
COLUMN m_bytes FORMAT 9999999.999
SET PAGES 34

SELECT file# ||','|| name || ',' || to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR') file_num_name_date, round(sum(bytes)/1024/1024, 4) m_bytes FROM v$datafile
GROUP BY ROLLUP(file# ||','|| name || ',' || to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR'))
ORDER BY file# ||','|| name || ',' || to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR');

I achieved what I wanted

but I'm looking at having 3 dimensions, namely file#, name, to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR')

and doing a rollup

https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets

SELECT file#, name, to_char(SYSTIMESTAMP, 'YYY-MM-DD HH24:MI:SS TZR'), GROUPING_ID(file#, name, to_char(SYSTIMESTAMP, 'YYY-MM-DD HH24:MI:SS TZR')) AS grouping_id , sum(bytes)/1024/1024 m_bytes FROM v$datafile GROUP BY GROUPING SETS (file#);

ERROR at line 1:
ORA-00979: not a GROUP BY expression

Is there a way to use grouping set instead of concatenation to achieve the rollup on one dimension only?

thanks a lot!

Report message to a moderator

Re: Is there a way to do a rollup on only one dimension [message #666756 is a reply to message #666755]

Fri, 24 November 2017 01:26

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

http://www.dpriver.com/pp/sqlformat.htm

Report message to a moderator

Re: Is there a way to do a rollup on only one dimension [message #666776 is a reply to message #666755]

Fri, 24 November 2017 07:51

Solomon Yakobson
Messages: 3254
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> SELECT  file#,
  2          name,
  3          to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS TZR') TS,
  4          GROUPING_ID(file#,name) AS grouping_id,
  5          sum(bytes)/1024/1024 m_bytes
  6    FROM  v$datafile
  7    GROUP BY ROLLUP(file#,name)
  8    HAVING grouping(file#) = grouping(name)
  9    ORDER BY file#
 10  /

     FILE# NAME                                                     TS                             GROUPING_ID    M_BYTES
---------- -------------------------------------------------------- ------------------------------ ----------- ----------
         8 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSTEM01.DBF  2017-11-24 08:49:58 -05:00               0        280
         9 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSAUX01.DBF  2017-11-24 08:49:58 -05:00               0        750
        10 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\UNDOTBS01.DBF 2017-11-24 08:49:58 -05:00               0        525
        11 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:58 -05:00               0     268.75
                                                                    2017-11-24 08:49:58 -05:00               3    1823.75

SQL> SELECT  file#,
  2          max(name) name, -- since file# is unique
  3          to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS TZR') TS,
  4          GROUPING_ID(file#) AS grouping_id,
  5          sum(bytes)/1024/1024 m_bytes
  6    FROM  v$datafile
  7    GROUP BY ROLLUP(file#)
  8    ORDER BY file#
  9  /

     FILE# NAME                                                     TS                             GROUPING_ID    M_BYTES
---------- -------------------------------------------------------- ------------------------------ ----------- ----------
         8 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSTEM01.DBF  2017-11-24 08:49:58 -05:00               0        280
         9 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSAUX01.DBF  2017-11-24 08:49:58 -05:00               0        750
        10 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\UNDOTBS01.DBF 2017-11-24 08:49:58 -05:00               0        525
        11 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:58 -05:00               0     268.75
           C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:58 -05:00               1    1823.75

SQL> SELECT  file#,
  2          name,
  3          to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS TZR') TS,
  4          GROUPING_ID(file#,name) AS grouping_id,
  5          sum(bytes)/1024/1024 m_bytes
  6    FROM  v$datafile
  7    GROUP BY grouping sets((),(file#,name))
  8    ORDER BY file#
  9  /

     FILE# NAME                                                     TS                             GROUPING_ID    M_BYTES
---------- -------------------------------------------------------- ------------------------------ ----------- ----------
         8 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSTEM01.DBF  2017-11-24 08:49:59 -05:00               0        280
         9 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSAUX01.DBF  2017-11-24 08:49:59 -05:00               0        750
        10 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\UNDOTBS01.DBF 2017-11-24 08:49:59 -05:00               0        525
        11 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:59 -05:00               0     268.75
                                                                    2017-11-24 08:49:59 -05:00               3    1823.75

SQL>

SY.

Report message to a moderator

Re: Is there a way to do a rollup on only one dimension [message #667950 is a reply to message #666776]

Thu, 25 January 2018 18:10

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Solomon,

thanks for your solution. it works like a charm

Report message to a moderator

Previous Topic:	load script for loading data from oracle table to excel
Next Topic:	need two token matching query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Oct 03 06:29:01 CDT 2023