Oracle:- PL/SQL – DECODE Function

Background

Reviewing someone’s SQL and needing to wrap my head around the Decode Function.

Transact SQL

Transact SQL does not offer the Decode Function and henceforth it always trips me.

 

Oracle – PL/SQL – Sample

DDL


create table state
(
     stateAbbrev varchar(2)
)

DML

DML – Data Add


delete from state;

insert into state
(
	  stateAbbrev      
)
select 'AR'
from   DUAL
union
select 'FL'
from   DUAL
union
select 'GA'
from   DUAL
union
select 'NY' 
from DUAL 
union
select 'TX'
from   DUAL
;

DML – Data Query

DML – Data Query – 01
Query
  1. Filler returned for unmapped entries


select 

      tblS.stateAbbrev as "state"
      
    , decode
        (
            tblS.stateAbbrev
          
            -- Arkansas
          , 'AR'
          , 'Bentonville, Little Rock'          
          
          -- Florida
          , 'FL'
          , 'Miami, Fort Lauderdale, Tampa'
          
          -- Georgia
          , 'GA'
          , 'Atlanta, Savannah'          
          
          -- Texas
          , 'TX'
          , 'Austin, Dallas, Houston, San Antonio'
          
        ) as "cities"
        
from   state tblS

Output

Explanation
  1. Null returned for unmatched entries

 

DML – Data Query – 02
Outline

Added support for un-mapped entries.

SQL


select 

      tblS.stateAbbrev as "state"
      
    , decode
        (
            tblS.stateAbbrev
          
            -- Arkansas
          , 'AR'
          , 'Bentonville, Little Rock'          
          
          -- Florida
          , 'FL'
          , 'Miami, Fort Lauderdale, Tampa'
          
          -- Georgia
          , 'GA'
          , 'Atlanta, Savannah'          
          
          -- Texas
          , 'TX'
          , 'Austin, Dallas, Houston, San Antonio'

         -- Uncategorized
          , 'Uncategorized'

        ) as "cities"
        
from   state tblS

Output

Explanation
  1. Filler returned for unmapped entries

 

Summary

Nothing magical.

The aim is to translate / map entries.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s