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.

Wednesday, August 26, 2015

Cumulative Count


This is similar to the post on cumulative sum using the windowing function. This was needed because we had an old application that would only accept a certain number of dist_id's at a time. In this report, you can see how many rows you can take in to only get 100 rows at a time.

SELECT dist_id
     , COUNT(*)                                             AS ctr
     , SUM(COUNT(*)) OVER ( ORDER BY COUNT(*)
                            ROWS BETWEEN UNBOUNDED PRECEDING
                                  AND CURRENT ROW
                          )                                 AS tot_count
  FROM ods_prd.xref_store
 WHERE master_oid = 1
 GROUP BY dist_id
 ORDER BY 2 ;

Wednesday, August 19, 2015

1000 = 1k


I found this bit of code when I was trying to write a report for work. I like it and wanted to save it as sometimes links seem to go away. So here it is for the second time.

Thanks to Vincent Malgrat on StackOverflow for this.
( http://stackoverflow.com/questions/19522582/1000000-to-1m-and-1000-to-1k-in-oracle-query )

From Oracle documentation: e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.
WITH data
     AS (SELECT POWER(10, ROWNUM) num
           FROM dual
         CONNECT BY LEVEL <= 9)
SELECT num
     , CASE
         WHEN num >= 1e6 THEN ROUND(num / 1e6) || 'M' -- 1e6 Exponentiation 
         WHEN num >= 1e3 THEN ROUND(num / 1e3) || 'k'
         ELSE TO_CHAR(num)
       END conv
  FROM data;

Monday, August 10, 2015

Making a Connection


If you want to change your login in your code, use the following statement. It can be either 'CONN' or 'CONNECT'.

I find this useful in code that you're possibly crossing a schema with a DB_Link and you want to be in one particular schema to start.
conn schema/password@database

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;
    

    Tuesday, July 28, 2015

    Outer Join with Where

    I may be the last one to figure this out, but a WHERE statement causes grief with an outer JOIN. Here I’m trying to join on a code table to get a description, but I only want those descriptions used for ‘Status,’ and not all records in Store have a status value.

    Doesn't Work

    Don't use ON key and WHERE condition.
    SELECT client_id                       AS "Client#"
         , cs.status                       AS "Status"
         , dc.cd_meaning                   AS "Description"
         , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
      FROM (
            SELECT client_id
                 , status
                 , COUNT(*)        AS dcnt
              FROM Store
             GROUP BY client_id, status
           ) cs
      LEFT
      JOIN def_codes dc
        ON cs.status = dc.cd
     WHERE column_name = 'STATUS';


    Works

    Use ON (key and AND condition).
    SELECT client_id                       AS "Client#"
         , cs.status                       AS "Status"
         , dc.cd_meaning                   AS "Description"
         , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
      FROM (
            SELECT client_id
                 , status
                 , COUNT(*)        AS dcnt
              FROM Store
             GROUP BY client_id, status
           ) cs
      LEFT
      JOIN def_codes dc
        ON ( cs.status = dc.cd
             AND
            column_name = 'STATUS'
           );

    Tuesday, July 14, 2015

    Where Case


    I wish I could think of a good reason to have a CASE statement in a WHERE statement -- but I can't so I'll just show something that works.

    One day...
    SELECT ename
         , job
         , deptno
      FROM scott.emp
    WHERE job LIKE
                  CASE
                     WHEN deptno = '20' THEN '%AL%'
                     WHEN deptno = '30' THEN '%MAN%'
                  END
    ORDER BY deptno, job
    ;

    Order By Case


    This is a simple example of using CASE to get a specific order in your output.
    SELECT ename
         , job
      FROM scott.emp
    ORDER BY CASE job
                   WHEN 'PRESIDENT' THEN 1
                   WHEN 'MANAGER'   THEN 2
                   WHEN 'ANALYST'   THEN 3
                   WHEN 'SALESMAN'  THEN 4
                                    ELSE 5
              END
    ;

    Friday, July 10, 2015

    Backup Tables in a Sandbox

    This script is to backup a set of common tables. The owner and table_name are converted to a shorter version so there is room to add a date on the end of the name.

    These are all written to a sandbox area, but are gathered from 5 different schemas.

    If this is written in Golden, the SETs have to be on separate lines.

    A report is produced at the end to show the names of the new tables.
    /* 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
    
    /* write SQL to this file from the following query */
    SPOOL h:\Bkup_Parm.sql
    
    -- Generate Create statements for all support_parameter tables
    SELECT 'CREATE TABLE sbx_str_uat.'
        || CASE owner
              WHEN 'ODS_UAT'     THEN 'ODSUAT__'
              WHEN 'SBX_STR_UAT' THEN 'SBXSTRU_'
              WHEN 'SBX_BRD_UAT' THEN 'SBXBRDU_'
              WHEN 'ODS_UAT1'    THEN 'ODSUAT1_'
              WHEN 'FSS_UAT'     THEN 'FSSUAT__'
             END
        || CASE table_name
              WHEN 'SUPPORT_PARAMETER_VALUES'  THEN 'SPV_'
              WHEN 'SUPPORT_PARAMETER_GROUP'   THEN 'SPG_'
              WHEN 'SUPPORT_PARAMETER_CHOICES' THEN 'SPC_'
              WHEN 'SUPPORT_PARAMETER'         THEN 'SP__'
             END
    
        || TO_CHAR(SYSDATE,'MMDDYYYY')
        || ' AS SELECT * FROM '
        || owner
        || '.'
        || table_name
        ||';'
      FROM all_tables
     WHERE REGEXP_LIKE(table_name , '^PARAMETER') -- starts with
       AND NOT REGEXP_LIKE(table_name,'[[:digit:]]') -- don't get other backups
     ;
    
    -- close this sql file
    SPOOL OFF
    
    -- run the code generated above
    @h:\Bkup_Parm
    
    -- list tables backed-up
    SELECT owner      AS "Owner"
         , table_name AS "UAT Parameter Backups"
      FROM all_tables
     WHERE table_name like '%_SP%'||TO_CHAR(SYSDATE,'MMDDYYYY')
     ORDER BY table_name;
    

    Tuesday, July 7, 2015

    Really Null Columns


    I was asked to find a column on a table that was null that we could use for another purpose. Since on the requested table, there is close to 200 fields, this didn't seem like a manual task so I wrote some code.

    All_Tab_Columns does have the Num_Distinct column, but our site doesn't always analyze each field so some of these say zero, but it's not true. So I start with that list and then check to see if they're truly zero by doing a count on the column.

    The first query will generate a spool file with contents that look something like the following: (The two single quotes are needed so the output has one single quote in it.)
    SELECT 'insert into nullcols values (''DIST_PHONE_INTL_CODE'');' 
      FROM (SELECT COUNT(DIST_PHONE_INTL_CODE) AS cnt 
              FROM ods_prd.xref_dist ) 
     WHERE cnt = 0 
    UNION ALL                                                                                   
    SELECT 'insert into nullcols values (''SP_NAME'');' 
      FROM (SELECT COUNT(SP_NAME) AS cnt 
              FROM ods_prd.xref_dist ) 
      WHERE cnt = 0;
    This then will spool to the second sql file an insert statement for any column that has a count of zero. When this piece runs, the column names get inserted into a temp table that is joined to All_Tab_Columns to find the type and length.
    
    -- Set In Golden, Press F5 to run all statements after tbl and sch are set
    
    DEFINE sch = ods_prd
    DEFINE tbl = xref_dist
    
    -- 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
    
    -- Nullcols is needed to hold the columns that needs to be compared to all_tab_columns
    -- Drop if it exists and then create.
    SPOOL h:\drop.sql
    SELECT 'DROP TABLE nullcols;'
      FROM all_tables
     WHERE owner = 'SBX_STR_PRD'
       AND table_name = 'NULLCOLS';
    SPOOL OFF
    @h:\drop
    
    CREATE TABLE nullcols (nullcol VARCHAR2(30));
    
    --write SQL to this file from the following query
    SPOOL h:\test.sql
    
    ------------------------------------------------------------------------------------
    -- Generate lines as shown to be run next
    -- insert into nullcols values ('CLIENT_CHAIN_ID');
    ------------------------------------------------------------------------------------
    WITH max_id AS
            -------------------------------------------------
            -- All lines end with 'UNION ALL' except the last
            -- which needs a ';'
            -------------------------------------------------
            (SELECT MAX(column_id) MAX_ID
               FROM all_tab_columns
              WHERE owner = UPPER('&sch')
                AND table_name = UPPER('&tbl')
                AND num_distinct = 0
             )
    SELECT 'SELECT ''insert into nullcols values ('''''
        || column_name
        || ''''');'
        || ''' FROM (SELECT COUNT('
        || column_name
        || ') AS cnt FROM &sch..&tbl ) WHERE cnt = 0'
        || CASE
             WHEN column_id = (SELECT max_id
                                 FROM max_id) THEN ';'
             ELSE ' UNION ALL'
           END
      FROM all_tab_columns
     WHERE owner      = UPPER('&sch')
       AND table_name = UPPER('&tbl')
       AND num_distinct = 0
     ORDER BY column_id;
    
    -- close this sql file
    SPOOL OFF
    
    -- spool a new sql file with the insert statements
    SPOOL h:\test2.sql
    
    -- run the code generated above
    @h:\test
    
    -- close this sql code file (insert statements)
    spool off
    
    -- run the insert statements
    @h:\test2
    
    -- produce a report that shows those columns and their
    -- type/length from all_tab_columns
    SELECT INITCAP(column_name) AS column_name
         , INITCAP(data_type)
           || CASE
                WHEN data_type <> 'DATE'
                THEN '('
                      || CASE
                           WHEN data_precision IS NULL
                           THEN data_length
                           ELSE TO_NUMBER(data_precision ||'.' || data_scale)
                         END
                      ||')'
              END AS type_length
      FROM all_tab_columns
     WHERE column_name IN (SELECT *
                             FROM nullcols)
       AND owner = UPPER('&sch')
       AND table_name = UPPER('&tbl');
    
    

    Thursday, June 25, 2015

    Generate Basic Select Except for All-Null Columns


    I’m building your standard Select statement, but I hate looking at null fields so I’m tossing those out. It does take a little while to generate depending on the size of the table and how many null fields there are. Like our client's table has about 20 null fields and 3.5M records and it took 3 minutes to write my query. I spool a generated SQL query to a .sql file and then execute it.
    
    DEFINE schema = fss_prd
    DEFINE tbl = client_table
    
    -- Press F5 --
    SET ECHO OFF
    SET FEEDBACK OFF
    SET VERIFY OFF
    SET HEADING OFF
    SET TIMING OFF
    SPOOL h:\test.sql
    
    SELECT 'SELECT CASE WHEN '
        || TO_CHAR(column_id)
        || '= 1 THEN ''SELECT '' ELSE ''     , '' END ||LOWER('''
        || column_name
        || ''') FROM &schema..&tbl WHERE ROWNUM < 2 AND '
        || column_name
        || ' IS NOT NULL UNION ALL'
      FROM all_tab_columns
     WHERE owner = UPPER('&schema')
       AND table_name = UPPER('&tbl')
     ORDER BY column_id
    ;
    SELECT 'SELECT ''  FROM &schema..&tbl;'' FROM DUAL;'
    FROM DUAL;
    
    SPOOL OFF
    
    @h:\test

    Sunday, June 7, 2015

    Partitioned Tables Report for a Schema


    This query -- once you adjust it for the schemas you’re interested in (and tables you’re not) -- will show pertinent information on the tables that are partitioned.

    As you may know, large tables are often partitioned. A partitioned table is actually many small tables joined into a larger one so that any one part can be read for speed or the entire table can be read if required. Reading a partition improves query performance.

    For example:
    SELECT *
      FROM ods_prd.log_helpdesk_error PARTITION (P568266); 

    You will usually need to find the latest partition needed for your query as they are constantly being added (and possibly dropped). This doesn’t necessarily apply to tables partitioned on dist_id or period_code.
    WITH atp
         AS (SELECT table_owner
                  , table_name
                  , partition_name
                  , partition_position
                  , num_rows
                  , blocks
                  , last_analyzed
               FROM (SELECT ROW_NUMBER() OVER (PARTITION BY table_owner
                                                          , p.table_name
                                                   ORDER BY partition_name DESC) AS row#
                          , p.*
                       FROM all_tab_partitions p
                      WHERE table_owner = 'ODS_PRD'
                        AND NOT REGEXP_LIKE(p.table_name, 'PROMO')
                        AND num_rows > 0
                    )
              WHERE row# = 1 -- shown only the last one
            )
    SELECT apt.owner
         , table_name
         , partitioning_type                     AS type
         , column_name
         , column_position                       AS col#
         , TO_CHAR(partition_count, '9,999,999') AS part#
         , status
         , partition_name                        AS last_name
         , TO_CHAR(num_rows, '999,999,999')      AS last_#_rows
         , TO_CHAR(last_analyzed, 'MM/DD/YYYY')  AS analyzed
      FROM all_part_tables apt
      JOIN all_part_key_columns apc
        ON apt.table_name = apc.name
       AND apt.owner = apc.owner
      JOIN atp
        ON apt.table_name = atp.table_name
       AND apt.owner = atp.table_owner
    WHERE apt.owner  = 'ODS_PRD'
       AND apc.owner = 'ODS_PRD' 
       AND NOT REGEXP_LIKE(table_name, 'PROMO')
       AND partition_count > 1 -- don't show tables with 1 partition
       AND num_rows > 0 -- don't show empty tables
    ORDER BY owner
            , table_name;

    Saturday, June 6, 2015

    Finding a Word in any Table that Stores Code


    This might come in handy if you have to find all the places in your code that a word is used. The following code generates a SQL query with multiple ‘Unions’ that can be executed to show all the tables and report IDs to be reviewed.

    SQL-Generating Code:

    /* change the word 'account' to the word you want to find in your code */
    DEFINE word = account
    
    SELECT 'SELECT '''                                                            -- literal 'select'
           || owner||'.'||table_name                                              -- owner.table name as literal in output
           || ''' AS tbl, report_id, report_title, report_desc, report_sql FROM ' -- literal column names and 'from'
           || owner||'.'||table_name                                              -- the table to pull the code from
           || q'[ WHERE REGEXP_LIKE(report_sql,'&word','i') UNION ALL]'           -- a literal piece of SQL code 
                                                                                  -- You must use 'UNION ALL' with CLOBs
      FROM all_tab_columns                                -- look here to find
     WHERE table_name like 'PREP%'                        -- all tables that start with prep
       AND column_name = 'REPORT_SQL'                     -- that have this field which holds our code
       AND data_type   = 'CLOB'                           -- we cannot union together CLOBs and VARCHAR2s
       AND NOT REGEXP_LIKE (table_name,'[[:digit:]]') ;   -- toss those tables that have backup dates on them
    

    SQL Code Generated:

    This will generate the following, which can be copied and pasted into the editor area. You'll have to get rid of the last 'Union all' before executing.
    SELECT 'SBX_STR_PRD.PREP_DR_ALTVIP' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_ALTVIP WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL
    SELECT 'SBX_STR_PRD.PREP_DR_CENROLL' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_CENROLL WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL
    SELECT 'SBX_STR_PRD.PREP_DR_DISTMNT' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_DISTMNT WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL
    

    Sample Report of Code to be Reviewed:

    It's easiest to paste this into Word to find and highlight code that might need to be changed.

    Tuesday, June 2, 2015

    Roll-up without Subtotals


    Rollup usually will show subtotals on every difference in the group. To show only a grand total, add 'grouping sets' and a '1' as shown below:
    
    SELECT deptno 
         , job 
         , SUM(sal) 
      FROM emp 
     WHERE deptno <> 10                  -- narrow down this example 
       AND job IN ( 'MANAGER', 'CLERK' ) -- narrow down this example 
     GROUP BY grouping sets ( ( deptno, job ) -- One row for every distinct combination of these 
                              , 1             -- Grand total super-aggregate 
                            ); 
    

    DEPTNO JOB SUM(SAL)
    30 CLERK 950
    20 CLERK 1900
    20 MANAGER 2975
    30 MANAGER 2850
    8675

    Or you can add 'Total' to it by decoding the 1st column. The Grouping value is 1 when it's a total.
    
    SELECT DECODE(
                  GROUPING(deptno) -- Decode the GROUPING value for DeptNo
                 ,1                -- If it is 1,
                 ,'Total'          -- Then print 'Total'
                 ,deptno           -- otherwise, print the actual DeptNo
                 ) AS dept         -- call this column 'Dept_Tot'
         , job
         , SUM(sal)
      FROM scott.emp
     WHERE deptno <> 10                  -- narrow down this example
       AND job IN ( 'MANAGER', 'CLERK' ) -- narrow down this example
     GROUP BY grouping sets ( ( deptno, job ) -- One row for every distinct combination of these
                              , 1             -- Grand total super-aggregate
                            );
    

    DEPT JOB SUM(SAL)
    30 CLERK 950
    20 CLERK 1900
    20 MANAGER 2975
    30 MANAGER 2850
    Total 8675

    Monday, June 1, 2015

    UAT vs PRD

    If you’re tired of switching your schemas in code from UAT to PRD, you can use ‘USER’, which will return the schema.
    
    -- create amper variable named environ
    COLUMN environ NEW_VALUE environ 
    
    SELECT SUBSTR(USER,-3) AS environ  -- get last 3 characters of schema
    FROM DUAL;



    SELECT *
      FROM xyz_ods_&environ..client_store /* use two dots
                                             1 for concatenation after variable and
                                             the normal 1 between table_name.owner */
    ;



    SELECT *   
      FROM xyz_sbx_str_&environ..master_map 
    ;
    

    Cumulative Sum


    To get a cumulative sum, you need to use a window function ‘Rows Between Unbounded Preceding and Current Row’, which tells Oracle that the window it’s summing is between the beginning and the current record.
    
    SELECT deptno, empno, ename, sal
         , SUM(sal) OVER (PARTITION BY deptno
                              ORDER BY empno
                                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                         ) AS
           cum_sal
      FROM scott.emp
     GROUP BY deptno, empno, ename, sal
     ORDER BY deptno, empno;
    

    Sunday, August 14, 2011

    Connecting to SQL Developer

    A while ago, the disk on my laptop crashed, and so now I'm trying to download and set up some of the tools I used before. I got SQL Developer installed, but naturally couldn't remember what I did to get the connection.  This time I decided to put it in writing.  I honestly haven't done any studying of Oracle's material on installation so it probably is all there, but one of the main reasons I blog is so I can find things again. 

    Here I'm connecting to Oracle's HR schema, using the password I selected when I installed the database.
    Click on 'Service name' at the bottom, and supply the three values found in your tnsnames.ora file.


    Voila! Click on 'Test' or 'Connect.'

    Thursday, August 11, 2011

    Wanna' Date?


    Just a picture of 99% of the date variations listed in the book, although I'm saving timestamps for another post.

    The Easy Way
     Select To_Char(Sysdate,'DL'   ) DL   /* long date */
          , To_Char(Sysdate,'DS'   ) DS   /* short date */
          , To_Char(Sysdate,'TS'   ) TS   /* short time */
          , To_Char(Sysdate,'DS TS') DSTS /* short date, time */
      From Dual;
    DL                        | DS       | TS         | DSTS                
    ------------------------- | -------- | ---------- | --------------------
    Saturday, August 06, 2011 | 8/6/2011 | 9:27:37 AM | 8/6/2011 09:27:37 AM

    Day
    Select To_Char(Sysdate,'DAY'   ) "DAY"    /* Name (Uppercase) */
         , To_Char(Sysdate,'Day'   ) "Day"    /* Name (Title case) */
         , To_Char(Sysdate,'DY'    ) "DY"     /* Abbreviated (Uppercase). */
         , To_Char(Sysdate,'Dy'    ) "Dy"     /* Abbreviated (Title case). */
         , To_Char(Sysdate,'D'     ) "D"      /* Day of week (1-7). */
         , To_Char(Sysdate,'DD'    ) "DD"     /* Day of month (1-31). */
         , To_Char(Sysdate,'fmDD'  ) "fmDD"   /* Day of month (1-31) zero suppress */
         , To_Char(Sysdate,'DDD'   ) "DDD"    /* Day of year (1-366). */
         , To_Char(Sysdate,'DDsp'  ) "DDsp"   /* spell out (Uppercase)*/
         , To_Char(Sysdate,'Ddsp'  ) "Ddsp"   /* spell out (Titlecase)*/
         , To_Char(Sysdate,'DDth'  ) "DDth"   /* ordinal (Uppercase) */
         , To_Char(Sysdate,'Ddth'  ) "Ddth"   /* ordinal (Title case) */
         , To_Char(Sysdate,'fmDdth') "fmDdth" /* ordinal zero suppress */
         , To_Char(Sysdate,'J'     ) "J"      /* Julian:# days since 1/1/4712BC */
      From Dual;

    DAY      | Day      | DY  | Dy  | D | DD | fmDD | DDD | DDsp | Ddsp | DDth | Ddth | fmDdth | J
    -------- | -------- | --- | --- | - | -- | ---- | --- | ---- | ---- | ---- | ---- | ------ | -------
    SATURDAY | Saturday | SAT | Sat | 7 | 06 | 6    | 218 | SIX  | Six  | 06TH | 06th | 6th    | 2455780

    Embed in Format - / , ; : . "text"
    Select To_Char(Sysdate,'"Day": Dy, "Month": Month')
      From Dual;

    TO_CHAR(SYSDATE,'"DAY":DY,
    --------------------------
    Day: Sat, Month: August


    Century

    Select To_Char(Sysdate,'AD')  "AD"   /* AD or BC */
         , To_Char(Sysdate,'A.D.')"A.D." /* A.D or B.C. */
         , To_Char(Sysdate,'B.C.')"B.C." /* A.D or B.C. */
      From Dual;

    AD | A.D. | B.C.
    -- | ---- | ----
    AD | A.D. | A.D.


    Month /* use fm in front to remove padding */

    Select To_Char(Sysdate,'MM')      "MM"     /* 01-12 */
         , To_Char(Sysdate,'Month')   "Month"  /* Name padded with blanks to widest name */
         , To_Char(Sysdate,'fmMonth') "fmMonth"/* Name not padded */
         , To_Char(Sysdate,'MONTH')   "MONTH"  /* NAME padded with blanks to widest name */
         , To_Char(Sysdate,'Mon')     "Mon"    /* Abbreviated name (title case) */
         , To_Char(Sysdate,'MON')     "MON"    /* Abbreviated name (uppercase) */
         , To_Char(Sysdate,'RM')      "RM"     /* Roman numeral month */
      From Dual;

    MM | Month     | fmMonth | MONTH  | Mon | MON | RM
    -- | --------- | ------- | ------ | --- | --- | ----
    08 | August    | August  | AUGUST | Aug | AUG | VIII


    Week

    Select To_Char(Sysdate,'WW')     "WW"    /* Week of year (1-53) where week 1 = 1/1 to 1/7. */
         , To_Char(Sysdate,'WWth')   "WWth"  /* Week of year (1ST-53RD) */
         , To_Char(Sysdate,'Wwth')   "Wwth"  /* Week of year (1st-53rd) */
         , To_Char(Sysdate,'WWsp')   "WWsp"  /* Week of year spelled out in caps */
         , To_Char(Sysdate,'Wwsp')   "Wwsp"  /* Week of year spelled out in title case*/
         , To_Char(Sysdate,'WWthsp') "WWthsp"/* Week of year (1st-53rd) spelled out*/
         , To_Char(Sysdate,'W')      "W"     /* Week of month (1-5) where week 1 = 1st to 7th*/
         , To_Char(Sysdate,'Wth')    "Wth"   /* Week of month (1st-5th) */
      From Dual;

    WW | WWth | Wwth | WWsp       | Wwsp       | WWthsp        | W | Wth
    -- | ---- | ---- | ---------- | ---------- | ------------- | - | ---
    32 | 32ND | 32nd | THIRTY-TWO | Thirty-Two | THIRTY-SECOND | 1 | 1st


    Quarter

    Select To_Char(Sysdate,'Q')     "Q"     /* Quarter of year (1=Jan-Mar, 2=Apr-Jun, etc)*/
         , To_Char(Sysdate,'Qth')   "Qth"   /* Quarter of year (1st - 4th */
         , To_Char(Sysdate,'Qsp')   "Qsp"   /* Quarter of year (One - Four) */
         , To_Char(Sysdate,'Qthsp') "Qthsp" /* Quarter of year (First - Fourth) */
      From Dual;

    Q | Qth | Qsp   | Qthsp
    - | --- | ----- | -----
    3 | 3rd | Three | Third


    Year

    Select To_Char(Sysdate,'Year')  "Year"  /* Spell out year */
         , To_Char(Sysdate,'RR')    "RR"    /* store 20th century dates in 21st using 2 digits */
         , To_Char(Sysdate,'RRRR')  "RRRR"  /* Round for 4 or 2. If 2, provides the same return as RR. */
         , To_Char(Sysdate,'Y,YYY') "Y,YYY" /* Year with comma */
         , To_Char(Sysdate,'YEAR')  "YEAR"  /* spelled out */
         , To_Char(Sysdate,'YYYY')  "YYYY"  /* 4-digit year */
         , To_Char(Sysdate,'YYY')   "YYY"   /* Last 3 digits */
         , To_Char(Sysdate,'YY')    "YY"    /* Last 2 digits */
         , To_Char(Sysdate,'Y')     "Y"     /* Last digit */
      From Dual;

    Year          | RR | RRRR | Y,YYY | YEAR          | YYYY | YYY | YY | Y
    ------------- | -- | ---- | ----- | ------------- | ---- | --- | -- | -
    Twenty Eleven | 11 | 2011 | 2,011 | TWENTY ELEVEN | 2011 | 011 | 11 | 1


    Time

    Select To_Char(Sysdate,'AM')    "AM"   /* Meridian indicator */
         , To_Char(Sysdate,'a.m.')  "a.m." /* Meridian indicator */
         , To_Char(Sysdate,'HH')    "HH"   /* Hour of day (1-12). */
         , To_Char(Sysdate,'fmHH')  "fmHH" /* Hour of day (1-12). */
         , To_Char(Sysdate,'HH24')  "HH24" /* Hour of day (0-23). */
         , To_Char(Sysdate,'MI')    "MI"   /* Minute (0-59). */
         , To_Char(Sysdate,'SS')    "SS"   /* Second (0-59). */
         , To_Char(Sysdate,'SSSSS') "SSSSS"/* Seconds past midnight (0-86399)*/
      From Dual;

    AM | a.m. | HH | fm | HH | MI | SS | SSSSS
    -- | ---- | -- | -- | -- | -- | -- | -----
    AM | a.m. | 09 | 9  | 09 | 27 | 37 | 34057

    Sunday, July 24, 2011

    My Ah-ha! Moment with Self-Joins

    I was recently taking an online class in SQL from Oracle. I will admit that sometimes self-joins still baffle my mind. So I decided to try what they said.

    Oracle's statement was:
    "To join a table to itself, the table is given two names or aliases. This will make the database “think” that there are two tables. Choose alias names that relate to the data's association with that table."

    So I decided to create two EMP tables in the Scott schema with just the information I needed.
    create table e_emp as select empno, ename, mgr from emp;
    create table m_emp as select empno, ename, mgr from emp;


    Now it's very easy to see that 'MGR' in the 'E' table will have to connect to 'EMPNO' in the 'M' table to get the report I want.

    So I'll code a very basic query from these two separate tables with the aliases 'E' and 'M' and will end up with the report shown below:

    Select e.empno as Emp#, e.ename as Employee
         , m.empno as Mgr#, m.ename as Manager
      from e_emp e
         , m_emp m
     where e.mgr = m.empno;

    But Oracle doesn't make me create two tables. I can run the same query listing EMP twice and get the same thing. The only thing to do is change the two table names 'e_emp' and 'm_emp' both to 'emp.'

    Select e.empno as Emp#, e.ename as Employee
         , m.empno as Mgr#, m.ename as Manager
      from emp e
         , emp m
     where e.mgr = m.empno;

    Saturday, July 23, 2011

    HR Hierarchy


    A friend asked me to explain the hierarchical query, and when I went back to look at what I had on the blog, there was little explanation, and more of just a sample for me to follow next time I had to write one. So I decided to redo it in the HR schema and add a few comments. All the special words used for hierarchical queries are shown in red.

    Line 3, 7, 8, 9 have to do with padding the name on the right with some dots. (for example: "3. Smith . . . .") Totally unnecessary, this was just for fun.

    Line 4 is where I'm indenting the level#. I don't want to indent level 1 so I first subtract 1 from the level. I then multiply by 3, but you can make this any number you want. In my case:
    Level 2 is indented by 3 spaces (2-1)*3
    Level 3 is indented by 6 spaces (3-1)*3
    Level 4 is indented by 9 spaces (4-1)*3
    (If this is going to be HTML output, you'll have to follow this post.)

    Line 5 concatenates a period and one space onto the back of the level number. (for example, "3. ")

    Line 6 concatenates the last name, a comma, a space, and the first name together. (for example: "King, Steven")

    Line 15 just got thrown in there to create a shorter output for this example.

    Line 16 and 17 say that we want to use Steven King as level 1.

    Line 18: I can't explain this better than Tom does in this post.

    Line 19 says if there is more than one sibling on a level, sort by last_name, and within that, first_name.

    /* 1*/ Select Employee_Id,
    /* 2*/        -----------------------------------------
    /* 3*/               Rpad(
    /* 4*/                      Lpad(' ',3*(Level-1))
    /* 5*/                    ||Level||'. '
    /* 6*/                    ||Last_Name||', '||First_Name
    /* 7*/                   ,30
    /* 8*/                   ,'. '
    /* 9*/                   )
    /*10*/               As
    /*11*/        Employee_Name,
    /*12*/        -----------------------------------------
    /*13*/        Manager_Id
    /*14*/   From Employees
    /*15*/  Where Department_Id Not in (50, 80)
    /*16*/  Start With Last_Name  = 'King'
    /*17*/         And First_Name = 'Steven'
    /*18*/Connect By Manager_Id = Prior Employee_Id
    /*19*/  Order Siblings By Last_name, First_name;
    
    

    Friday, July 22, 2011

    SQL*Plus Output -> HTML

    *I saw this on Uwe Hesse's blog, and it was too cool not to keep a copy where I knew I could find it again.  The only thing I added to his script was the 'Start' in front of 'Firefox' that's needed by Windows (and the comment about removing the 'l').

    HTML.SQL:
    set termout off

    set markup HTML ON HEAD " -
     -
    " -
    BODY " " -
    TABLE "border='1' align='center' summary='Script output'" -
    SPOOL ON ENTMAP ON PREFORMAT OFF

    spool myoutput.html

    --------------------------------------------------------------------------
    -- if you want your query to display in firefox, uncomment the 'l' below.
    --------------------------------------------------------------------------
    --l
    /

    spool off
    set markup html off spool off
    host start firefox myoutput.html
    set termout on

    Run your query in SQL+.

    conn hr/hr
    set linesize 2000 pagesize 60 feedback off

    Select *
      From Employees
     Where Job_Id = 'SH_CLERK'
     Order by Last_Name;
    Hmm.. this is a bit too much for the screen.  You can either start formatting with 'Column' or run HTML.SQL.
    Beautiful! Thanks Uwe Hesse and Tanel Poder.

    Wednesday, July 13, 2011

    Oracle Proprietary vs. ANSI-99 Joins


     Oracle Proprietary JoinsANSI-99 SQL Joins
    To display only rows with matching Keys
    • "Equi-Join" aka "Simple Join" aka "Inner Join"
    • The WHERE clause states how to join the tables
    • Use AND to specify other conditions (i.e., AND City = 'DC')
    Select L.Key, NameL, NameR     
      From TableL L
         , TableR R
     WHERE L.Key = R.Key;
    • Natural Join
    • Can't use aliases
    1) Key Name and Type are the same
    Select Key, NameL, NameR
      From TableL 
    NATURAL
      JOIN TableR;
    2) Key Name Same, Different Type
    Select Key, NameL, NameR
      From TableL
      JOIN TableR
     USING (Key);
    3) Different Key Names
    Select Key, Key3, NameL, Name3
      From TableL
      JOIN Table3
        ON (Key = Key3);
    "Non-Equi-Join": Keys are >, <, or BETWEEN
    Select L.Key, R.Key
         , NameL, NameR
      From TableL L
         , TableR R
     Where L.Key > R.Key;
    Select L.Key, R.Key, NameL, NameR     
      From TableL L
      JOIN TableR R
        ON (l.Key > r.Key); 
    Joining More Than 2 tables
    • There is always one less WHERE/AND clause than there are tables to join. 
    (3 tables = 2 clauses, etc)
      Select Key, NameL, NameR, Name3
        From TableL l
           , TableR r
           , Table3 t
       Where l.Key = r.Key
         And l.Key = t.Key
      • USING when names are the same.
      • ON when names are different
      Select Key, NameL, NameR, Name3
        From TableL L
        -----------
        Join TableR 
       Using (Key)
        -----------
        Join Table3 
          On (l.Key = Key3); 
      To see unmatched rows “Right Outer Joins”
      Select L.Key, NameL, NameR
        From TableL L
           , TableR R
       Where L.Key(+) = R.Key  ;
      (Think of "Add rows to the left side so we see a full set of records on the right.")
      Select Key, NameL, NameR
        From TableL 
      RIGHT OUTER
        JOIN TableR 
       Using (Key);

      To see unmatched rows: “Left Outer Join”
      Select L.Key, NameL, NameR     
        From TableL L
           , TableR R
       Where L.Key = R.Key(+);
      (Think of "Add rows to the right side so we see a full set of records on the Left.")
      Select Key, NameL, NameR
        From TableL 
      LEFT OUTER
        JOIN TableR
       Using (Key);
      To see all unmatched rows "Full Outer Join"
      Oracle doesn’t have a full outer join.
      Select l.Key, r.Key, NameL, NameR
        From TableL L  
      FULL OUTER
        JOIN TableR R
        on (l.Key = r.Key);

      CROSS JOIN is the ANSI name for Oracle's Cartesian Product -- Don't do this no matter what you call it.

      Monday, July 11, 2011

      Multi-Table Inserts -- Just the Basics

      You can use a multi-table insert when data from one table can be used to populate several tables. This is something often seen in a data warehouse.

      They can be unconditional or conditional, but not both. To do both, you can 'fake' it out by adding 'When 1=1 Then' to unconditional inserts.

      If you want to see this work, you can copy the code shown below. It will run in the Scott schema.

      • Unconditional multi-table insert inserts all rows selected in the subquery into all of the table insert clauses.
      • Conditional mutli-table insert: you can specify ALL (default) or FIRST.
        • ALL - evaluates each WHEN
        • FIRST - execute the first WHEN (top to bottom), and then none below it.
          • . With the FIRST statement, you can have an ELSE clause to perform if no WHENs are true.
          • If there is no ELSE, no action is performed on that row.
      conn Scott/Tiger
      ----------------------------------------
      -- Drop these tables if already created.
      ----------------------------------------
      Drop Table Sal_Hist;
      Drop Table Big_Sal;
      
      ------------------------------------
      -- Create empty tables to hold data.
      ------------------------------------
      CREATE TABLE Sal_Hist 
      AS 
      SELECT EmpNo , Job , Sal 
        FROM Emp 
        WHERE 1 = 2; 
        
      CREATE TABLE Big_Sal  
      AS 
      SELECT EmpNo , Sal 
        FROM Emp 
       WHERE 1 = 2; 
      
      ------------------------------------
      -- Conditional Mutli-Table Insert
      ------------------------------------
      Insert All
        When 1 = 1 Then  -- always insert
        Into Sal_Hist
        Values (EmpNo, Job, Sal)
      -----------------------------------------
        When Sal >= 3000 Then -- conditionally insert
        Into Big_Sal
        Values (EmpNo, Sal)
      -------------------------------------------
      Select EmpNo , Job , Sal  -- These are the columns pulled
        From Emp                -- that can potentially be inserted 
       Where DeptNo = 20;       -- in the newly-created tables.
      
      ------------------------------------
      -- check to see what got inserted
      ------------------------------------
      Select Count(*) 
        from Emp
       Where DeptNo = 20;
       
      Select * From Sal_Hist;
      Select * From Big_Sal;
        
      ------------------------------------
      -- Delete to try insert #2
      ------------------------------------
      Delete From Sal_Hist;
      Delete from Big_Sal;
      
      ------------------------------------
      -- Unconditional Mutli-Table Insert
      -- (Just remove the 'WHEN' clauses)
      ------------------------------------
      Insert All
        Into Sal_Hist
        Values (EmpNo, Job, Sal)
      -----------------------------------------
        Into Big_Sal
        Values (EmpNo, Sal)
      -------------------------------------------
      Select EmpNo , Job , Sal
        From Emp
       Where DeptNo = 20;
      
      ------------------------------------
      -- check to see what got inserted
      ------------------------------------
      Select * From Sal_Hist;
      Select * From Big_Sal;

      Sunday, July 10, 2011

      To Sum It Up...

      ROLLUP, CUBE, GROUPING SETS and GROUPING can be used to subtotal and total aggregates based on the values in the GROUP BY clause.

      ROLLUP generates subtotals and totals (for example, salary by departments, and the total salary for the report).  If there is more than one field listed in the GROUP BY ROLLUP clause, the subtotals move from right to left.

      This shows the basic 'GROUP BY' which will give subtotals for the dept, but no grand total.
      SELECT DeptNo
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
       GROUP BY DeptNo;
      
      DEPTNO   SUM(SAL)
      ------ ----------
          30       9400
          20      10875
      If 'GROUP BY' is changed to 'GROUP BY ROLLUP' it adds a grand total to the subtotals.
      SELECT DeptNo
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
       GROUP BY ROLLUP (DeptNo);
      
      DEPTNO   SUM(SAL)
      ------ ----------
          20      10875
          30       9400
                  20275
      The GROUPING function can be used to note which lines are subtotals or totals.

      This first example is used to show the settings of GROUPING.
      SELECT DeptNo
           , SUM(Sal)
           , GROUPING(DeptNo)
           , DECODE(
                    GROUPING(DeptNo) -- Decode the GROUPING value for DeptNo
                   ,1                -- If it is 1,
                   ,'Total'          -- Then print 'Total'
                   ,DeptNo           -- otherwise, print the actual DeptNo
                   ) as Dept_Tot     -- call this column 'Dept_Tot'
        FROM Emp
       WHERE DeptNo <> 10
       GROUP BY ROLLUP (DeptNo);
      
      DEPTNO   SUM(SAL) GROUPING(DEPTNO) DEPT_TOT
      ------ ---------- ---------------- -------------
          20      10875                0 20
          30       9400                0 30
                  20275                1 Total
      As you can see, when the GROUPING value for the DeptNo is '1,' this is a total. Otherwise, this is the actual DeptNo number.

      Obviously, we wouldn't want our report to list DeptNo twice, so I would move this around to the front. And of course, the GROUPING value doesn't need to be displayed at all.
      Column Dept_Tot Format A13
      
      SELECT DECODE(GROUPING(DeptNo),1,'Total',DeptNo) as Dept_Tot
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
       GROUP BY ROLLUP (DeptNo);
      
      DEPT_TOT        SUM(SAL)
      ------------- ----------
      20                 10875
      30                  9400
      Total              20275
      Here's a query where we want subtotals based on two values. Since ROLLUP goes right to left, the first subtotal will actually be the GROUP BY for the Job. After that, comes the DeptNo subtotal, and then the Grand Total.  This example, doesn't decode the GROUPING values.
      SELECT DeptNo
           , Job
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
       GROUP BY ROLLUP (DeptNo
                       ,Job
                       );
      
      DEPTNO JOB         SUM(SAL)
      ------ --------- ----------
          20 CLERK           1900
          20 ANALYST         6000
          20 MANAGER         2975
          20                10875
          30 CLERK            950
          30 MANAGER         2850
          30 SALESMAN        5600
          30                 9400
                            20275
      Now, lets add the headings into this report using DECODE.

      Since the GROUP BY has already subtotaled the job, When the GROUPING value of Job is  set to '1', it will actually be for the higher group, which is the department.

      When the GROUPING value of DeptNo is '1,' it is for the higher group, which is the entire report.
      Column Job_Tot Format A16
      
      SELECT DECODE(GROUPING(DeptNo),1,'* Grand Total',DeptNo) as Dept_Tot
           , DECODE(GROUPING(Job   ),1,'* Dept Total' ,Job   ) as Job_Tot
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
       GROUP BY ROLLUP (DeptNo
                       ,Job
                       );
      
      DEPT_TOT      JOB_TOT            SUM(SAL)
      ------------- ---------------- ----------
      20            CLERK                  1900
      20            ANALYST                6000
      20            MANAGER                2975
      20            * Dept Total          10875
      30            CLERK                   950
      30            MANAGER                2850
      30            SALESMAN               5600
      30            * Dept Total           9400
      * Grand Total * Dept Total          20275
      This is close to what I want, but I don't want it to say 'Dept Total' on the Grand total line.  I'm going to switch from DECODE to CASE because I think it's going to be easier to understand.

      Since I've put the DeptNo in the Total line, I'm going to drop that column.
      SELECT Case When GROUPING(Job)    = 1
                   And Grouping(DeptNo) = 0
                  Then '* Total: Dept '||DeptNo
                  --
                  When GROUPING(Job)    = 1
                   And GROUPING(DeptNo) = 1
                  Then '** Grand Total'
                  Else Job
              End    as Job_Tot
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
       GROUP BY ROLLUP (DeptNo
                       ,Job
                       );
      
      JOB_TOT            SUM(SAL)
      ---------------- ----------
      CLERK                  1900
      ANALYST                6000
      MANAGER                2975
      * Total: Dept 20      10875
      CLERK                   950
      MANAGER                2850
      SALESMAN               5600
      * Total: Dept 30       9400
      ** Grand Total        20275

      CUBE: Cross-Tabulation


      The Cube statement does a cross-tabulation, which means every possible combinations of rows is aggregated.  I'm going to narrow down the output based on job to make it simpler to view.  Also, I'm going to display the GROUPING values for the two columns.
      SELECT DeptNo
           , Grouping(DeptNo)
           , Job
           , Grouping(Job)
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
         AND Job In ('MANAGER','CLERK')
       GROUP BY CUBE (DeptNo
                     ,Job
                     );
      As shown, there are four combinations of the GROUPING values.
      DEPTNO GROUPING(DEPTNO) JOB       GROUPING(JOB)   SUM(SAL)
      ------ ---------------- --------- ------------- ----------
                            1                       1       8675 <-- grand
                            1 CLERK                 0       2850 <-- all clerks
                            1 MANAGER               0       5825 <-- all managers
          20                0                       1       4875 <-- dept 20
          20                0 CLERK                 0       1900 <-- clerks in dept 20
          20                0 MANAGER               0       2975 <-- managers in dept 20
          30                0                       1       3800 <-- dept 30
          30                0 CLERK                 0        950 <-- clerks in dept 30
          30                0 MANAGER               0       2850 <-- managers in dept 30
      Based on that, we can write a total statement about each condition showing the actual values. It's padded with dots to the right.
      Column Tot_Hdr Format A50
      
      SELECT Rpad(
                  Case When Grouping(DeptNo) = 1
                        And Grouping(Job)    = 1
                       Then '*** Grand Total'
                       --
                       When Grouping(DeptNo) = 1
                        And Grouping(Job)    = 0
                       Then '    ** Total for All '||Job ||'s'
                        --
                       When Grouping(DeptNo) = 0
                        And Grouping(Job)    = 1
                       Then '    ** Total for dept '|| DeptNo
                        --
                       When Grouping(DeptNo) = 0
                        And Grouping(Job)    = 0
                       Then ' * Total for '|| Job || 's in Dept ' || DeptNo
                  End
                 ,50  -- the end of Rpad
                 ,'. '
                 )               As Tot
             --------------------------------------------------------
           , To_Char(
                     Sum(Sal)
                    ,'$9,999.99'
                    )            As Tot_Sal
        FROM Emp
       WHERE DeptNo <> 10               -- narrow down this example
         AND Job In ('MANAGER','CLERK') -- narrow down this example
       GROUP BY Cube (DeptNo
                     ,Job
                     );
      
      TOT                                                          TOT_SAL
      ------------------------------------------------------------ ----------
      *** Grand Total. . . . . . . . . . . . . . . . . .            $8,675.00
          ** Total for All CLERKs. . . . . . . . . . . .            $2,850.00
          ** Total for All MANAGERs. . . . . . . . . . .            $5,825.00
          ** Total for dept 20. . . . . . . . . . . . .             $4,875.00
             * Total for CLERKs in Dept 20. . . . . . .             $1,900.00
             * Total for MANAGERs in Dept 20. . . . . .             $2,975.00
          ** Total for dept 30. . . . . . . . . . . . .             $3,800.00
             * Total for CLERKs in Dept 30. . . . . . .               $950.00
             * Total for MANAGERs in Dept 30. . . . . .             $2,850.00
      Here's the same type of thing with three fields:
      SELECT DeptNo
           , Grouping(DeptNo)
           , Job
           , Grouping(Job)
           , To_Char(Hiredate,'YYYY') As HireYr 
           , Grouping(To_Char(Hiredate,'YYYY')) As Grouping_Yr
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10
         AND Job In ('MANAGER','CLERK')
         AND To_Char(Hiredate,'YYYY') In ('1981','1987') -- narrow down this example
       GROUP BY Cube (DeptNo
                     ,Job
                     ,To_Char(Hiredate,'YYYY')
                     );
      
      DEPTNO GROUPING(DEPTNO) JOB       GROUPING(JOB) HIRE GROUPING_Yr SUM(SAL)
      ------ ---------------- --------- ------------- ---- ----------- --------
                            1                       1               1     8675
                            1                       1 1981          0     6775
                            1                       1 1987          0     1100
                            1 CLERK                 0               1     2850
                            1 CLERK                 0 1981          0      950
                            1 CLERK                 0 1987          0     1100
                            1 MANAGER               0               1     5825
                            1 MANAGER               0 1981          0     5825
          20                0                       1               1     4875
          20                0                       1 1981          0     2975
          20                0                       1 1987          0     1100
          20                0 CLERK                 0               1     1900
          20                0 CLERK                 0 1987          0     1100
          20                0 MANAGER               0               1     2975
          20                0 MANAGER               0 1981          0     2975
          30                0                       1               1     3800
          30                0                       1 1981          0     3800
          30                0 CLERK                 0               1      950
          30                0 CLERK                 0 1981          0      950
          30                0 MANAGER               0               1     2850
          30                0 MANAGER               0 1981          0     2850

      Column Tot format A60
      
      SELECT RPad(
                  Case When Grouping(Deptno)                   = 1
                        And Grouping(Job)                      = 1
                        And Grouping(To_Char(Hiredate,'YYYY')) = 1
                       Then '****Grand Total'
                       ----------------------
                       -- Those with two '1's
                       ----------------------
                       When Grouping(Deptno)                   = 1
                        And Grouping(Job)                      = 1
                        And Grouping(To_Char(Hiredate,'YYYY')) = 0
                       Then '    ***Total of people hired in '||To_Char(Hiredate,'YYYY')
                       --
                       When Grouping(Deptno)                   = 1
                        And Grouping(Job)                      = 0
                        And Grouping(To_Char(Hiredate,'YYYY')) = 1
                       Then '    ***Total for '||InitCap(Job)||'s'
                       --
                       When Grouping(Deptno)                   = 0
                        And Grouping(Job)                      = 1
                        And Grouping(To_Char(Hiredate,'YYYY')) = 1
                       Then '    ***Total for Department '||DeptNo
                       ---------------------
                       -- Those with one '1'
                       ---------------------
                       When Grouping(Deptno)                   = 1
                        And Grouping(Job)                      = 0
                        And Grouping(To_Char(Hiredate,'YYYY')) = 0
                       Then ' **Total for '||InitCap(Job)||'s hired in '
                                        ||To_Char(Hiredate,'YYYY')
                       --
                       When Grouping(Deptno)                   = 0
                        And Grouping(Job)                      = 1
                        And Grouping(To_Char(Hiredate,'YYYY')) = 0
                       Then ' **Total for Department '||DeptNo||' hired in '
                                            ||To_Char(Hiredate,'YYYY')
                       --
                       When Grouping(Deptno)                   = 0
                        And Grouping(Job)                      = 0
                        And Grouping(To_Char(Hiredate,'YYYY')) = 1
                       Then ' **Total for Department '||DeptNo||'''s '||InitCap(Job)||'s'
                       ---------------------
                       -- Those with no '1'
                       ---------------------
                       When Grouping(Deptno)                   = 0
                        And Grouping(Job)                      = 0
                        And Grouping(To_Char(Hiredate,'YYYY')) = 0
                       Then '   *Total for Department '||DeptNo||'''s '||InitCap(Job)
                                          ||'s hired in '||To_Char(Hiredate,'YYYY')
                   End
                 ,60     -- the end of the RPad
                 ,'. '
                 )               As Tot
            ---------------------------------------------------------------------------------
           , To_Char(
                     Sum(Sal)
                    ,'$9,999.99'
                    )           As Tot_Sal
        FROM Emp
       WHERE DeptNo <> 10                                -- narrow down this example
         AND Job In ('MANAGER','CLERK')                  -- narrow down this example
         AND To_Char(Hiredate,'YYYY') In ('1981','1987') -- narrow down this example
       GROUP BY Cube (DeptNo
                 ,Job
                 ,To_Char(Hiredate,'YYYY')
                 );
      
      TOT                                                          TOT_SAL
      ------------------------------------------------------------ ----------
      ****Grand Total. . . . . . . . . . . . . . . . . . . . . . .  $7,875.00
          ***Total of people hired in 1981. . . . . . . . . . . .   $6,775.00
          ***Total of people hired in 1987. . . . . . . . . . . .   $1,100.00
          ***Total for Clerks. . . . . . . . . . . . . . . . . . .  $2,050.00
             **Total for Clerks hired in 1981. . . . . . . . . . .    $950.00
             **Total for Clerks hired in 1987. . . . . . . . . . .  $1,100.00
          ***Total for Managers. . . . . . . . . . . . . . . . . .  $5,825.00
             **Total for Managers hired in 1981. . . . . . . . . .  $5,825.00
          ***Total for Department 20. . . . . . . . . . . . . . .   $4,075.00
             **Total for Department 20 hired in 1981. . . . . . .   $2,975.00
             **Total for Department 20 hired in 1987. . . . . . .   $1,100.00
             **Total for Department 20's Clerks. . . . . . . . . .  $1,100.00
               *Total for Department 20's Clerks hired in 1987. .   $1,100.00
             **Total for Department 20's Managers. . . . . . . . .  $2,975.00
               *Total for Department 20's Managers hired in 1981.   $2,975.00
          ***Total for Department 30. . . . . . . . . . . . . . .   $3,800.00
             **Total for Department 30 hired in 1981. . . . . . .   $3,800.00
             **Total for Department 30's Clerks. . . . . . . . . .    $950.00
               *Total for Department 30's Clerks hired in 1981. .     $950.00
             **Total for Department 30's Managers. . . . . . . . .  $2,850.00
               *Total for Department 30's Managers hired in 1981.   $2,850.00

      GROUPING SETS

      GROUPING SETS allows multiple 'GROUP BY' clauses in the same result set. It does not display all the totals shown in the rollup and cube.
      This could be a large savings in processing if it works to grab both sets of data at the same time instead of making two passes through the database.
      SELECT DeptNo
           , Job
           , To_Char(Hiredate,'YYYY') As HireYr
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10                                -- narrow down this example
         AND Job In ('MANAGER','CLERK')                  -- narrow down this example
         AND To_Char(Hiredate,'YYYY') In ('1981','1987') -- narrow down this example
       GROUP BY GROUPING SETS ((DeptNo, Job)
                              ,(Job   , To_Char(Hiredate,'YYYY'))
                              );
      
      DEPTNO JOB       HIRE   SUM(SAL)
      ------ --------- ---- ----------
          20 MANAGER              2975
          20 CLERK                1100
          30 CLERK                 950
          30 MANAGER              2850
             CLERK     1981        950
             MANAGER   1981       5825
             CLERK     1987       1100
      This is the same as the following two queries (in output -- not processing time).
      SELECT DeptNo
           , Job
           , SUM(Sal)
        FROM Emp
       WHERE DeptNo <> 10                     
         AND Job In ('MANAGER','CLERK')       
       GROUP BY DeptNo, Job;
      
          DEPTNO JOB         SUM(SAL)
      ---------- --------- ----------
              20 CLERK           1900
              20 MANAGER         2975
              30 CLERK            950
              30 MANAGER         2850

      SELECT  Job
           , To_Char(Hiredate,'YYYY') As HireYr
           , SUM(Sal)
        FROM Emp
       WHERE Job In ('MANAGER','CLERK')       
         AND To_Char(Hiredate,'YYYY') In ('1981','1987') 
       GROUP BY Job, To_Char(Hiredate,'YYYY');
      
      JOB       HIRE   SUM(SAL)
      --------- ---- ----------
      CLERK     1981        950
      MANAGER   1981       8275
      CLERK     1987       1100