SQL Decode – Evaluating Expressions and Conditions on a Case-By-Case Basis

shutterstock_94754395If you are familiar with If-Then-Else statements, then you may find that the concepts involved with an If-Then-Else statement can be somewhat similar to concepts involved with the SQL Decode function. If you are not familiar with If-Then-Else statements or the Decode function in SQL, that is also okay, because this article will provide explanations and examples for the SQL Decode function. The Decode function evaluates an expression to determine which criteria options are met and then performs an action (such as overriding a value) based on the evaluation results; the function will perform these steps using a process that involves cases for different criteria.

An index for a table can help improve searches and optimizations. Not only does the Decode function assist with data handling, it also combines the benefits of indexes. Additionally, the Decode function can help with organization of a table as it can create new columns used for data distribution based on results from evaluations. Conditions that are included in a Decode function include conditions such as the Equal(=) condition and Less Than(<) or Greater Than(>) condition.

The Decode function can be used along with Select statements for queries, such as a CrossTab query. A CrossTab query involves operations that are common with a Select statement, but also have added functionality for distributing data in a table. Using a CrossTab query along with a Decode function is one method that utilizes the organizational benefits of the Decode function. You can learn more about different types of queries in SQL by exploring a tutorial on Udemy.

Using the Decode Function

The following is an outline for the fundamental syntax for the Decode function:

DECODE (<object/value to be evaluated>,
IF <first condition met>, THEN <action to be performed>
IF<next condition met>, THEN < action to be performed>
<default value to be set if none of the conditions have been met>)

There is also an updated version of the Decode function, called the Case function. The operations and syntax for the Case and Decode functions are similar, but the Case function has more capabilities than the Decode function. The following is an outline of the fundamental syntax for the Case function:

CASE when <object/value to be evaluated>
WHEN <meets this criteria>, THEN <action to be performed>
ELSE <default action>
END

As a demonstration of the application of the syntax summaries provided above, the following is an example of a value being overridden after a Decode or Case function is implemented:

Scenario 1

In this scenario, a smoothie shop is replacing all of the peanut butter smoothies with almond butter smoothies. The table for the shop is called Smoothie_Tbl and contains columns for menu item number, description, and price. The values being evaluated will be the values for the DESCRIPTION column.

MENU_ITEMDECRIPTIONPRICE
1Strawberry$3.00
2Tropical$4.00
3Strawberry Banana$4.00
4Blueberry$3.00
5Raspberry$3.00
6Chocolate Banana$4.00
7Carob Banana$5.00
8Peanut Butter$4.00
9Green Tea$4.00
10Carrot$3.00
11Carrot Ginger$5.00
12Kale$3.00

 

A SELECT statement along with the Decode function may be used as follows:

SELECT
DECODE
(DESCRIPTION, ‘Peanut Butter’,
‘Almond Butter’,
DESCRIPTION)
as
Revised_Menu,
PRICE
FROM Smoothie_Tbl;

Note that the description column is being focused on, the first value evaluate is “Peanut Butter” and the next value (for the THEN portion of the statement) is “Almond Butter”, the next value is Description (for the ELSE portion of the statement), Revised_Menu is the new column created for the resulting values to be distributed in, and the Price column is listed so that it will be included along with the newly created column. The statement can be summed up by the following interpretation: “Begin the Select statement, use the Decode function, search through the DESCRIPTION column, look for a value that matches ‘Peanut Butter’, if ‘Peanut Butter’ is found then override that value to ‘Almond Butter’, else the value being searched for is not found then the Description column will have the original values, display the resulting data for DESCRIPTION in a column named Revised_Menu and then include the PRICE values for the associated table, all of the data being handled is from the table named Smoothie_Tbl, and now end the Select statement.”

The following is how the SELECT statement would look when used along with the Case function:

SELECT CASE
WHEN DESCRIPTION = ‘Peanut Butter’ THEN ‘Almond Butter’
ELSE
DESCRIPTION
END
AS Revised_Menu,
PRICE
FROM
Smoothie_Tbl;

 

Output:

REVISED_MENU    PRICE
Strawberry$3.00
Tropical$4.00
Strawberry Banana$4.00
Blueberry$3.00
Raspberry$3.00
Chocolate Banana$4.00
Carob Banana$5.00
Almond Butter$4.00
Green Tea$4.00
Carrot$3.00
Carrot Ginger$5.00
Kale$3.00

 

Scenario 2

After the smoothie shop reviews the revised menu, they realize that they will lose profit by offering the Almond Butter smoothie at the same price that the Peanut Butter smoothie had been offered due to higher costs associated with Almond Butter. They consider the Almond Butter smoothie to be one of their “specialty smoothies” and increase the price of the smoothie to $5.00. To demonstrate how the Decode function can be used to update values from more than one column, you will begin with the original table and then use the function to evaluate the values for the descriptions column as well as for the price column.

SELECT DECODE
(DESCRIPTION, ‘Peanut Butter’,
‘Almond Butter’,
DESCRIPTION) AS Revised_Menu ,
DECODE
(DESCRIPTION, ‘Peanut Butter’, 5.00, PRICE)
AS PRICE
FROM
Smoothie_Tbl;

Note that the overridden value for the DESCRIPTION column is designated with a new column name of Revised_Menu and that the second Decode function also evaluates the DESCRIPTION column using the initial value of “Peanut Butter” before designating the column to have an associated value of 5.00 for the overridden value of the PRICE column; the reason that the original value is referenced in the second Decode function is because the value had not been overridden at that point. The value will be overridden after completion of the query.

SELECT CASE
WHEN DESCRIPTION = ‘Peanut Butter’ THEN ‘Almond Butter’
ELSE DESCRIPTION
END AS Revised_Menu,
CASE
WHEN DESCRIPTION = ‘Peanut Butter’
THEN 5.00
ELSE PRICE
END AS
PRICE
FROM Smoothie_Tbl;

Output:

REVISED_MENU    PRICE
Strawberry$3.00
Tropical$4.00
Strawberry Banana$4.00
Blueberry$3.00
Raspberry$3.00
Chocolate Banana$4.00
Carob Banana$5.00
Almond Butter$5.00
Green Tea$4.00
Carrot$3.00
Carrot Ginger$5.00
Kale$3.00

 

Scenario 3

In this scenario, you will see an example that demonstrates the option to have a message associated with a value. The smoothie shop has priced all of its specialty smoothies with a price of more than $4.00. In this example, it has been assumed that the original table data has been revised and a Decode function will be used in order to add a “Specialty Smoothie” message for all smoothies from the table that have a price of more than $4.00. Being able to display data after it has been revised and having other abilities, such as, adding descriptions for data can help with building reports; you can find out more about reporting capabilities that SQL presents by taking a class on Udemy.

SELECT
DESCRIPTION,
PRICE,
DECODE
(SIGN(PRICE – 4.00),
+1, ‘Specialty Smoothie’,
0, ‘ ‘,
-1, ‘ ‘,
null, ‘ ‘)
AS MESSAGE
FROM Smoothie_Tbl;

Note that if the price is more than 4.00, then the message is added but if the price is not more than 4.00 or less than 4.00, then a message is not added. If you prefer to use the Case function, an example of a Case function that could be used in this scenario is as follows:

SELECT
DESCRIPTION,
PRICE,
CASE WHEN PRICE > 4.00
THEN ‘Specialty Smoothie’
ELSE ‘   ‘
END AS MESSAGE
FROM Smoothie_Tbl;
DECRIPTION  PRICEMESSAGE
Strawberry$3.00
Tropical$4.00
Strawberry Banana$4.00
Blueberry$3.00
Raspberry$3.00
Chocolate Banana$4.00
Carob Banana$5.00Specialty Smoothie
Almond Butter$5.00Specialty Smoothie
Green Tea$4.00
Carrot$3.00
Carrot Ginger$5.00Specialty Smoothie
Kale$3.00

 

Conclusion

The Decode function provides a relatively adaptable way to perform actions associated with other functions and can make queries more powerful. The more powerful a query is, the more likely that it may be that it will be able to add functionality that will be essential with analytics. If you are interested in becoming more involved with SQL analytics and are fascinated by the inner workings of SQL, then you should find the tutorials available on Udemy to be quite useful. The Decode function also helps with organizing the structure of data in tables. When you have the option to use the Case function, it may be used instead of the Decode function because it provides more options than the Decode function. Overall, the Decode function is useful with basic queries as well as relatively complex tasks.