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.

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