Count a Field in Varying Tables with Subtotals

This code was written like this because the table names were not constant in which I need a count of a certain field. This sets variables for

Environment (Production or UAT)

Partition Name

Output Excel Name

A count of tables found

Once I got it done, I decided I wanted totals so a 'Create table' had to be added to the generated code so I could go back and decode the grouping values.

--SQL+ Commands to leave only SQL statements in the spool file
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF

-- set for either environment (Prd/UAT)
COLUMN env NEW_VALUE env
SELECT SUBSTR(USER,-3) AS env
  FROM DUAL;

-- get latest partitions for MDSs
COLUMN part NEW_VALUE part
SELECT 'P'||MAX(ctl_master_del_store_oid) part
  FROM fss_&env..ctl_master_store;

-- save a file name for the excel export
COLUMN file_name NEW_VALUE file_name
SELECT '\\fs2\ims\Stores\VIP_Year_Count_'
    || TO_CHAR(SYSDATE,'YYYYMMDD') AS file_name
  FROM DUAL;

-- Find # of tables for union vs ';' in next query
COLUMN max_line NEW_VALUE max_line
SELECT COUNT(*)     AS max_line
  FROM dba_tab_columns      dtc
  JOIN dba_tables           dt
 USING (owner, table_name)
 WHERE column_name = 'VIP_YEAR'
   AND dt.num_rows > 0               -- ignore empty tables
   AND owner IN ( 'ODS_&env'
                , 'FSS_&env'
                , 'FEED_&env'
                );

--write SQL here from the following query to execute afterwards
SPOOL h:\Count_VIP_Years.sql

SELECT ------------------------------------------------------------------------
       -- Create table so a rollup can be done. Add to 1st line
       -- Add Union to each line except the last which takes a ';'
       ------------------------------------------------------------------------
       CASE WHEN line_no = 1 then 'CREATE TABLE sbx_str_&env..vip_year AS ' END
    || sql_line
    || CASE WHEN line_no < &max_line THEN ' union ' ELSE ';  ' END
  FROM (
        SELECT 'SELECT ''' || owner||'.'||table_name || ''' AS tbl '
            || ', TO_NUMBER(vip_year)                       AS vip_year '
            || ', COUNT(*)                                  AS cnt '
            || 'FROM '|| owner||'.'||table_name
            || CASE WHEN table_name LIKE 'MASTER_STORE%' -- only MSs have partitions
                    THEN ' PARTITION (&PART)' END
            || ' GROUP BY vip_year'
                                                            AS sql_line
             , ROW_NUMBER() OVER (PARTITION BY NULL
                                      ORDER BY owner DESC
                                             , table_name
                                 )                          AS line_no -- to find last line
         FROM dba_tab_columns dtc
         JOIN dba_tables      dt
        USING (owner, table_name)
        WHERE dtc.column_name = 'VIP_YEAR'
          AND dt.num_rows > 0             --get rid of empty tables
          AND owner IN ( 'ODS_&env', 'FSS_&env', 'FEED_&env' )
        ORDER BY owner DESC
               , table_name
       );

-- close this sql file
SPOOL OFF

-- run the code generated above to generate vip_year table in the sbx
@h:\Count_VIP_Years

-- run this against the vip_year table created in count_vip_years.sql
SELECT INITCAP(tbl) AS "Table"
     , vip_year     AS "VIP Year"
     , cnt          AS "Count"
  FROM (
        SELECT tbl
             , GROUPING(tbl)                                            AS tblgrp
             , GROUPING(vip_year)                                       AS yrgrp
             , DECODE(GROUPING(vip_year), 1, '* Table Total', vip_year) AS vip_year
             , TO_CHAR(SUM(cnt), '999,999,999')                         AS cnt
          FROM vip_year
         GROUP BY rollup ( tbl, vip_year )
       )
 WHERE tblgrp||yrgrp <> 11 -- do not display grand total
 ORDER BY tbl DESC
        , CASE vip_year
              WHEN '* Table Total'
              THEN TO_NUMBER(9999)        -- display total last
              ELSE TO_NUMBER(vip_year)
          END nulls first;

-- Export to Excel
EXPORT EXCEL [NONE] ["&file_name"]

-- Cleanup
DROP TABLE sbx_str_&env..vip_year;

-- Message
SELECT '\\fs2\ims\Stores\VIP_Year_Count_'
    || TO_CHAR(SYSDATE,'YYYYMMDD')
    || '.xlsx'         AS "Your file has been written to:"
  FROM DUAL;