Decode Function

I will admit that this isn't the best name Oracle could have given to this function. This is a very powerful SQL function that can really help developers. Its main purpose is to provide a pseudo if statement inside an sql statement. The decode statement takes the following parameters:

decode(text_to_compare,value_to_compare,result_if_true,result_if_false);

Let's see decode in action to firm up everyone's understanding.

1. select decode('test','test','They are equal!','Um.. not so much.') from dual;  
2.   
3. DECODE('TEST','TEST','THEY ARE EQUAL!','UM..NOT SO MUCH.')   
4. ------------------------------------------------------   
5. They are equal!   

1 rows selected


As you can see, when 'test' equals 'test' then we get 'They are equal!'. Okay, so that's pretty boring on a good day. Let's dig in a more complex example. I have a table called decode_test for obvious reasons that contains information about products that have been sold by yours truly.

1. select name, product_id, decode(product_id,  
2.               1,'Stereo System',  
3.               2,'Blu-Ray DVD Player',  
4.               3,'Brick of Gold',  
5.               'Other stuff laying around store.') from decode_test;  
6.   
7.   
8. NAME                 PRODUCT_ID             PRODUCT_NAME                       
9. -------------------- ---------------------- --------------------------------   
10. Nicholas             1                      Stereo System                      
11. Nicholas             3                      Brick of Gold                      
12. Nicholas             2                      Blu-Ray DVD Player                 
13. Nicholas             1                      Stereo System                      
14.   
15. 4 rows selected  

Okay so what happened here? Basically in my test, each product_id represented some text I wanted to display in there query. You'll notice decode can take multiple value pairs to test against the input text.

So I'll admit the previous example won't win any "Example of the Year" awards. So here is a more practical use of decode that involves dates and conditional summation.

1. select name,   
2. sum(decode(to_char(date_sold,'MMYYYY'),current_month,amount_sold,null)) as sales_month_to_date,   
3. sum(decode(to_char(date_sold,'YYYY'),current_year,amount_sold,null)) as sales_year_to_date  
4. from decode_test,  
5. (select to_char(sysdate,'MMYYYY') as current_month,   
6.         to_char(sysdate,'YYYY') as current_year from dual) d  
7. group by name;  
8.   
9. NAME                 SALES_MONTH_TO_DATE    SALES_YEAR_TO_DATE       
10. -------------------- ---------------------- ----------------------   
11. Nicholas             67.5                   132.5                    
12.   
13. 1 rows selected  

Well, well, that was halfway slick. In the previous example, I needed to sum only the sales for the current month and for year to date. Decode helped me to sum the amount_sold, when it was appropriate.