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.

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

    Monday, April 18, 2011

    RANK vs DENSE_RANK

    If you're not familiar with how RANK works, you may want to check out my previous post by clicking here.

    This post shows the difference between RANK and DENSE_RANK.
    Select DeptNo
         , Sal
         ,             RANK() OVER (Partition By DeptNo
                                        Order By Sal  )
           Rank
         ,       DENSE_RANK() OVER (Partition By DeptNo
                                        Order By Sal  )
           Dense
      From Emp
     Where DeptNo <> 20
     Order By DeptNo, Rank;
     
    Here you can see that RANK leaves a gap after a tie and DENSE_RANK does not.

    Sunday, April 17, 2011

    Rank and File

    Someone asked me the other day how to pull the top-n number of rows from a table.  Of course, I thought of the old way of sorting rows in a subquery and then pulling based on rownum. For example, if you want to show records for the top three salaries, you could code:
    Select *
      From ( Select *
               From Emp
              Order By Sal Desc )
      Where RowNum < 4;
    RANK is a better option -- and it can be used for a few other neat tricks. There are two variations on RANK syntax: analytical and aggregate.

    TypeBasic SyntaxExample
    Aggregate
    RANK (expr) WITHIN GROUP
    (Order By ) 
    How does the guy that makes 5000 in salary rank within the group?
    Analytic #1
    RANK ( )    OVER
    (Order By )
    Rank departments based on total salary in each department.
    Analytic #2
    RANK ( )    OVER 
    (Partition By
         Order By )
    Show the rank based on each person's salary within each department.


    Aggregate Example:

    SQL> break on deptno skip 1
    SQL> select * from emp order by deptno, sal; 











    Based on the data shown above, if I want to know the rank of the person in department 20 with a salary of 1100, I would code:
    Select RANK (              20 ,  1100 ) WITHIN GROUP
                (Order By  DeptNo ,  Sal  ) 
      From Emp;
    
    RANK(20,1100)WITHINGROUP(ORDERBYDEPTNO,SAL)
    -------------------------------------------
                                             5
    Obviously, all those spaces aren't necessary in the code. I just put them in to show that the values in the 'RANK' line need to match the columns in the 'Order By' line.

    Analytic Example #1:

    Rank departments based on total salary. (The rank doesn't need to be included in the select.)
    Select          RANK() OVER (Order By Sum(Sal) )
         , DeptNo
         , Sum(Sal)
      From Emp
     Group By DeptNo
     Order By       RANK() OVER (Order By Sum(Sal) );













    Analytic Example #2:


    Show the rank based on each person's salary within each department.
    Select DeptNo
         , Sal
         ,       RANK() OVER (Partition By DeptNo 
                                  Order By Sal  ) 
           Rank
      From Emp
     Where DeptNo <> 20
     Order By DeptNo, Rank;


    A tie will display the same ranking, but then the next ranking value will be skipped.

    Monday, February 14, 2011

    Flashback a Table, Index and Trigger Script

    This script performs a flashback of tables, indexes and triggers. If the item already exists, it will be renamed with the base_object number.


    1) After displaying a list of tables that are in the Recycle Bin, the script will ask for a table to be flashbacked.

    2) If a table with that name already exists, a message is displayed stating that the base number will be added to the name.

    3) A report is then displayed showing indexes and triggers that will also be reestablished.

    4) The report labeled 4 shows the items recreated.

    5) Lets you know what else might have to be done.

    Click here for a downloadable attachment.
    Define OnOff = Off
    rem +--------------------------------------------------------------------------+
    rem | Script ID:     FlashTbl.sql
    rem |
    rem | Purpose:       This performs a flashback of tables, indexes and triggers.
    rem |                If the table already exists, it will be renamed with the
    rem |                base_object number.
    rem |
    rem | Developer:     Lynn Tobias
    rem | Script Date:   August 08, 2008
    rem |
    rem | Input File(s): none
    rem |
    rem | Table(s) Used: Dual, User_Recyclebin
    rem |                
    rem | Called by:     n/a
    rem | Calls:         FlashObj.Sql (created in this script)
    rem |
    rem | Variables:     OnOff          - Used to debug
    rem |                Dropped_Table  - Name of table to flashback
    rem |                Ctr            - if table found in user_table then '1'
    rem |                Renamed_Phrase - the Renamed_Phrase phrase of flashback 
    rem |                                 if a table already exists
    rem |                New_Name       - the name of the table with the base number
    rem |                                 if Ctr=1 otherwise,original name
    rem | Output:        n/a
    rem |
    rem | Revisions:     Dvl Date     Ver Comment/Change
    rem |                --- -------- --- -----------------------------------------+
    rem |                
    rem +--------------------------------------------------------------------------+
    
    --Clear Screen
    
    --+- 1 -------------------------------------------------------------------------
    --| Show the dropped tables available for selection
    --+-----------------------------------------------------------------------------
    
    Set Heading On Newpage 2 Linesize 200 Pagesize 60 Term On
    Define xSubHead = '    DROPPED TABLES IN USER_RECYCLEBIN'
    
    Ttitle -
    Left 'User: '  _user         col 68 'F L A S H B A C K' col 120 'Date:  ' _DATE  skip 1 -
    Left 'Script: ' FlashTbl.Sql col 55 xSubHead            col 120 'Page: ' sql.pno skip 2
    
    Column Object_Name    Heading Object|Name          Format A15 
    Column Original_Name  Heading Original|Name        Format A30
    Column Operation      Heading Oper-|ation          Format A5
    Column Type           Heading Type                 Format A7
    Column Ts_Name        Heading T(bl)|S(pc)|Name     Format A5
    Column Createtime     Heading Create|Time          Format A10
    Column Droptime       Heading Drop|Time            Format A10
    Column Dropscn        Heading Drop|Scn             Format 9,999,999
    Column Partition_Name Heading Part-|ition|Name     Format A5
    Column Can_Undrop     Heading Can|Undrop           Format A6
    Column Can_Purge      Heading Can|Purge            Format A5
    Column Related        Heading Related              Format 99,999
    Column Base_Object    Heading Base|Object          Format 99,999
    Column Purge_Object   Heading Purge|Object         Format 99,999
    Column Space          Heading Space|(Blocks)       Format 999
    
    Select Object_Name,  Original_Name, Operation,   Type,           Ts_Name,    
           Createtime,   Droptime,      Dropscn,     Partition_Name, Can_Undrop, 
           Can_Purge,    Related,       Base_Object, Purge_Object,   Space
      From User_Recyclebin
     Where Type = 'TABLE'
     Order By Original_Name;
    
    Ttitle Off
    
    --+- 2 -------------------------------------------------------------------------
    --| Ask for a table to flashback
    --+-----------------------------------------------------------------------------
    
    Accept Dropped_Table Prompt 'Name of table to flashback: '
    
    --+- 3 -------------------------------------------------------------------------
    --| If that tablename already exists, CTR is set to '1'. Post a message.
    --+-----------------------------------------------------------------------------
    
    Set Heading Off NewPage 1
    Column Ctr New_Value Ctr NoPrint 
    
    Select Case When Count(*) = 1
                Then 'Note: A table with that name exists. This will be renamed with base#.'
                Else ' '
            End ,
          --------------------------------------------------------------------------
          Count(*) As Ctr
          --------------------------------------------------------------------------
     From User_Tables
    Where Table_Name = Upper('&Dropped_Table')
    /
    Prompt
    --+- 4 ------------------------------------------------------------------------
    --| Pull everything that needs to be fixed (table, indexes, triggers).
    --| Also, determine the new name if a liked-name table already exists.
    --+-----------------------------------------------------------------------------
    
    Create Global Temporary Table GT_Flash
      On Commit Preserve Rows
      As
    --------------------------------------------------
    Select urb.*,  
           --------------------------------------------- 
                   Case When &Ctr = 1 
                        Then Substr(
                                    Original_Name||'_'||Base_Object
                                   ,1,30
                                    )
                        Else Original_Name
                    End 
                    As 
           New_Name
      From User_Recyclebin urb
     Where Base_Object = 
                        --+- 4.1 ------------------------------------------------
                        --| Get the latest base# where it's the table requested
                        --| This is necessary as there may be more than one table
                        --| with the name selected.
                        --+------------------------------------------------------
                       (
                        Select Max(Base_Object)
                          From User_Recyclebin
                         Where Original_Name  = Upper('&Dropped_Table')
                       );
    
    --+- 5 -------------------------------------------------------------------------
    --| If Ctr = 1, build the 'rename' portion of the flashback statement. 
    --+-----------------------------------------------------------------------------
    
    Set Term &OnOff
    Column Renamed_Phrase New_Value Renamed_Phrase
    
    Select          Case When &Ctr = 1 
                         Then 'Rename To '||New_Name
                         Else ' '
                      End 
                      As 
           Renamed_Phrase
      From GT_Flash
     Where Original_Name  = Upper('&Dropped_Table');
    
    Set Term On
    
    --+- 6 ------------------------------------------------------------------------
    --| Display the other objects (Triggers, Indexes)
    --+-----------------------------------------------------------------------------
    
    Set Newpage 2
    Ttitle On
    Define xSubHead = 'ITEMS THAT WILL BE REESTABLISHED AFTER FLASHBACK'
    Set Heading On
    
    Select *
      From GT_Flash
     Where Original_Name != Upper('&Dropped_Table')  -- everything but the table
    ;
    
    --+- 7 -------------------------------------------------------------------------
    --| Build the statements to rebuild indexes and triggers. Get the Base_Object 
    --| since there can be more than one with that name. (Base_Object (and RELATED) 
    --| will match across tables, indexes and triggers.)
    --|
    --| Examples:
    --| ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
    --| ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;
    --+-----------------------------------------------------------------------------
    
    Spool FlashObj.Sql
    
    Ttitle Off
    Set Heading &OnOff Feedback &OnOff Verify &OnOff Term &OnOff
    
    Select 'Alter ' || Type || ' "' || Object_Name || '" Rename To ' ||New_Name ||' ;'
      From GT_Flash
     Where Original_Name != Upper('&Dropped_Table') -- everything but the table
    ;
    
    Spool Off
    
    --+- 8 -------------------------------------------------------------------------
    --| Flashback the table
    --+-----------------------------------------------------------------------------
    
    Flashback Table &Dropped_Table To Before Drop &Renamed_Phrase
    /
    --+- 9 -------------------------------------------------------------------------
    --| Bring back indexes and triggers
    --+-----------------------------------------------------------------------------
    
    @FlashObj
    
    --+- 9 -------------------------------------------------------------------------
    --| Show them anything in User_Objects that is also in our global table
    --+-----------------------------------------------------------------------------
    
    Set Term On Heading On
    
    Ttitle On
    Column Object_Name Format A30
    
    Define xSubHead = 'ITEM(S) RECOVERED AND NOW FOUND IN USER_OBJECTS';
    
    Select Object_Name, Object_Type, Created, Status
      From User_Objects
     Where Object_Name In 
                         --+- 9.1 --------------------------------------------------
                         --| Find out if there is an active table with that name
                         --+--------------------------------------------------------
                         (
                         Select New_Name
                           From GT_Flash
                         )
    ;
    
    --+- 10 ------------------------------------------------------------------------
    --| Now bring the bad news...
    --+-----------------------------------------------------------------------------
    
    Prompt
    Prompt Follow-up:
    Prompt =========
    Prompt Views and procedures defined on the table are not recompiled and remain in the invalid state. -
    These old names must be retrieved manually and applied.
    Prompt
    Prompt Dropped bitmap indexes are not placed in the recycle bin. Constraint names are also not retrievable.
    Prompt
    
    --+- 10 ------------------------------------------------------------------------
    --| Clean up
    --+-----------------------------------------------------------------------------
    
    Truncate Table GT_Flash;
       Drop Table GT_Flash;
    
    Undefine Dropped_Table Ctr Renamed_Phrase New_Name    
    Ttitle Off