On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Saturday, August 1, 2015

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;
    

    No comments:

    Post a Comment