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.