Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     Oracle:

 
Using decode and case functions
 
The most powerful of all of the BIFs are the decode and case functions.  The decode and case functions are used within the Oracle database to transform data values for one value to another. 
 
One of the most amazing features of the case the decode statements is that they allow us to create an index on data column values that do not exist in the database.
 
Oracle started with the decode statement and later refined it in Oracle9i, morphing it into the case statement.
 
 
Let’s take a look at how the decode statement works.  The decode statement was developed to allow us to transform data values at retrieval time.  For example, say we have a column named REGION, with values of N, S, W and E.  When we run SQL queries, we want to transform these values into North, South, East and West.  Here is how we do this with the decode function:
 
select
   decode (
      region,
     ‘N’,’North’,
     ‘S’,’South’,
     ‘E’,’East’,
     ‘W’,’West’,
     ‘UNKNOWN’
   )
from
   customer;
 
Note that decode starts by specifying the column name, followed by set of matched-pairs of transformation values.  At the end of the decode statement we find a default value.  The default value tells decode what to display if a column values is not in the paired list.
 
We can also use the decode statement to count the number of distinct values within a data column, such as the report below: 
 
PUBLISHER_NAME         COMPUTER    FICTION MANAGEMENT MISCELLANEOUS      MUSIC 
-------------------- ---------- ---------- ---------- ------------- ---------- 
Big City                      0          1          0             1          0 
Book Press                    0          0          0             1          0 
Bookhouse                     2          0          0             1          1 
Desk Top                      0          0          1             0          0 
Learning Works                1          0          1             0          0 
Mammoth House                 1          1          0             1          0 
Mountain                      0          2          0             0          0 
Nitpick And Sons              1          0          1             1          0 
Star Books                    0          0          1             1          0
 
Here is the SQL to create this report in your pubs database:
 
select
   initcap(substr(pub_name,1,20))            publisher_name,
   sum(decode(book_type,'computer',1,0))     computer,
   sum(decode(book_type,'fiction',1,0))      fiction,
   sum(decode(book_type,'management',1,0))   management,
   sum(decode(book_type,'miscellaneous',1,0)) miscellaneous,
   sum(decode(book_type,'music',1,0))        music
from
   publisher p,
   book      b
where
   p.pub_key = b.pub_key
group by
   pub_name
;
 
 
This is a very important SQL statement because it demonstrates the nesting of BIFs and the use of decode for counting values.  Let’s take a closer look.
 
1 – Transformation of publisher name - In the above SQL we see that we are selecting the first 20 characters of the pub_name (substr(pub_name,1,20)) and then passing this result to the initcap function to make the words display in title case.
 
initcap(          ç change to title case
   substr(        ç take the sub-string
      pub_name,   ç of the pub_name column
      1,          ç starting at column 1
      20          ç for 20 columns
      )
)
 
 
2 – Transformation  of book type - When we decode book_type, note that we are transforming the value to a 1 if the column is found, and a 0 if it is not found. 
 
decode(
   book_type,         ç Column name
   ‘computer, 1       ç Matched pair – if computer, then 1
   0                  ç default value if not computer
   )
 
Once we have converted the column to a 0-1 numeric value, we pass the entire clause to the sum function, and add-up the numbers.
 
As we can see, the decode function is convoluted and hard to write.  Oracle added the case function to SQL starting in Oracle9i to simplify this type of data transformation. The case statement is an easier for of the decode statement.  In its simplest form, the case statement is used to return a value when a match is found:
 
SELECT
   last_name,
   commission_pct,
  (CASE commission_pct
    WHEN 0.1 THEN ‘Low’
    WHEN 0.15 THEN ‘Average’
    WHEN 0.2 THEN ‘High’
    ELSE ‘N/A’
  END ) Commission
FROM
   employees
ORDER BY
   last_name;
 
 
In the above example, we see that we display “Low” when the commission percent is .1, “Average” when it is .15, and “High” when it is .2.  This syntax is far more readable than the cryptic decode function.
 
The case statement can also be used to perform complex Boolean matches. A more complex version is the Searched CASE expression where a comparison expression is used to find a match:
 
SELECT
   last_name,
   job_id,
   salary,
  (CASE
    WHEN job_id LIKE 'SA_MAN'  AND salary < 12000  THEN '10%'
    WHEN job_id LIKE 'SA_MAN'  AND salary >= 12000 THEN '15%'
    WHEN job_id LIKE 'IT_PROG' AND salary < 9000   THEN '8%'
    WHEN job_id LIKE 'IT_PROG' AND salary >= 9000  THEN '12%'
    ELSE 'NOT APPLICABLE'
  END ) Raise
FROM
   employees;
 
 
In this example we see that we test for specific job titles and salaries, and display a percentage based upon these values.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.