Understanding Decode Function In SQL

Question

Can someone please explain me how the decode function works in SQL ? I am trying to understand this line

DECODE(table1.R1_REGION, 'Y','Y',DECODE(table1.R2_REGION,'Y','Y','N')) R1_OR_R2_REGION

I am not sure how this field R1_OR_R2_REGION is getting populated using the decode fn.

Answer 1

DECODE is explained in documentation; in simple cases, it is easy to understand. When things get complex, DECODE becomes real pain to write, maintain and understand.

Lucky us, we have CASE which is way simpler. Perhaps it would be easier for you to understand if DECODE you posted is rewritten to the following expression:

#1 case when table1.r1_region = 'Y' then 'Y'
#2     else case when table1.r2_region = 'Y' then 'Y'
#3               else 'N'
            end
   end r1_or_r2_region

That's exactly what DECODE does (match numbers in CASE with these in DECODE):

DECODE(table1.R1_REGION, 'Y','Y',DECODE(table1.R2_REGION,'Y','Y','N')) 
       ------------------------  ------------------------------  ---                      
       #1                        #2                              #3

• if table1.r1_region is equal to Y, then the result is Y
• otherwise, check whether table1.r2_region is equal to Y
  • if so, the result is Y
  • otherwise, result is N