The Decode function in Oracle

     Oracle has a function called decode that allows you to decode coded values or even encode values depending on your needs. The function takes a column, a series of if-then pairs and a default value. An example is in order. Let's think back to our Recipe database example from earlier posts. You have a Table called Recipe and it has a column called Type to show if the recipe is an Appetizer or a Soup or a Dessert for example. And let's say that in Version 1 of our recipe database we just made RECIPE.TYPE a CHAR(1) column. So, we store 'A' for Appetizer, 'S' for Soup and 'D' for Dessert, etc. Now, let's say your Mom/SO/Daughter/Client is confused by the 'S' on the screen and why Salads show an 'L' (for obvious reasons we cannot have both Soup and Salad designated with an S unless we want to combine them so the 'S' designates a Soup or a Salad. For this example, we want to keep them separate, and 'A' was taken for Appetizer so the next available letter in Salad is the 'L', but you don't want to explain every one of them. You can write a SQL statement with a Decode and list out all of them one time and use that where you need it. Our statement would look something like:

SELECT NAME, DECODE(TYPE, 'A', 'Appetizer', 'S', 'Soup', 'D', 'Dessert', 'L', 'Salad', 'Other') AS Decoded_Type FROM RECIPE;

This is pretty handy. Now, let's say you want to run a report and see how many different type recipes you have. One possible query, using multiple decodes within an aggregate would be:

SELECT
  SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers,

  SUM(DECODE(TYPE, 'S', 1, 0)) as NumberOfSoups,

  SUM(DECODE(TYPE, 'D', 1, 0)) as NumberOfDesserts,

  SUM(DECODE(TYPE, 'L', 1, 0)) as NumberOfSalads,

  SUM(DECODE(TYPE, 'A', 0, 'S', 0, 'D', 0, 'L', 0, 1)) as NumberOfOthers
FROM RECIPE;

Now, the way SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers, reads is: for every record, I want to select 1 if the TYPE = 'A' and 0 Otherwise, Oh, and, please sum up the total of that for me and call it NumberOfAppetizers. This might not be the best way to do this for our example, but I ran across a real world case where someone had written a huge SQL statement where the calculated those values in sub-selects, and then joined all of those sub-selects together to display the desired information. Although the above is a much simplified version it could be expanded upon to utilize DECODE, alleviate the multiple sub-select join, and improve readability and performance.

A couple of other things we hit upon this time that might not have seen before is the nested function calls and the column aliasing. Functions, whether SQL Native or After-Market, depending on what they do and your use-case or needs, can be nested where the inner function executes and returns result to the outer function which then executes. We see that with the decode statements and the sum functions. The decode converts the type to either a 1 or 0 for each row and then the results are summed for the entire table. Pretty neat stuff. I have used this a lot with dates - ADD_MONTHS(TRUNC(SYSDATE), 1) or something like that. The column aliasing lets you rename columns in a select statement. SELECT TYPE AS MY_TYPE, SUM(AMOUNT) AS PROFIT FROM RECIPE GROUP BY TYPE; etc.

I feel this might be a slippery slope here because my explanations keep introducing new things. ADD_MONTHS is a function that takes a date and returns the passed value moved n months into the future or past depending on sign. Yes, ADD_MONTHS('01-DEC-2012', -12) will return '01-DEC-2011'. Sysdate, I believe we have covered before but just in case we haven't that is the system date from the oracle server. TRUNC is a function that truncates part of the date value that you pass depending on what you pass. TRUNC(SYSDATE) drops the time portion and returns the current day month and year. Other options exist, like TRUNC(SYSDATE, 'MM') returns the first day of the current month, etc. Another new item is the GROUP BY statement. If you notice the absence of GROUP BY in the earlier example, that is because I did not select anything that was not being aggregated. That is the rule with GROUP BY in Oracle. You must group by everything that you are not aggregating. If you are aggregating everything you do not need the GROUP BY. So, in the PROFIT example, I am selecting TYPE and the SUM of AMOUNT so I must GROUP BY TYPE or Oracle tells me TYPE is not a group by expression.

Check out the Oracle Function Docs for more information.

I'll dig into more interesting Oracle SQL statements, problems and/or solutions in the future. And, I might even lose my shit and go off on a rant. Toodles.