Introduction to Teradata CASE Statement
Teradata’s CASE statement is used to check for various conditions and that also in a sequential manner in order to produce the clustering results based on which conditional is met first. If none of the specified conditions is met then the result in the output specified by the ELSE statement will be considered by Teradata’s CASE Statement. If there is no ELSE Clause in the CASE Statement, then the result of the CASE Statement not matching any of the mentioned cases will be NULL.
So, if you have many multiple conditionals to check and you need to apply the very first conditional in sequence then use the CASE statements.
What is Teradata’s CASE Statement?
It provides conditional executions of the statements that consist of :
- WHEN Clause
- ELSE Clause
- END
WHEN Clause contains all of the conditionals specifically in sequence followed by the statements that will be executed once these conditionals are met
- These conditionals are sequential and once, one of them is met then the case statement ends there
- Otherwise, it moves to the next WHEN statement if in case we have multiple WHEN statements specified under the same CASE.
- When so ever a conditional is met, the CASE statement exits.
ELSE Clause, contains the results that should be in place if none of the conditionals in the WHEN clause is met. This is defined at the end of the CASE statement, just before the END Clause.
END Clause is used to mark the end of the case statement and is usually followed by an alias name that is given to the field created by this CASE statement.
Examples to Implement Teradata CASE Statement
Let’s take up an example in order to understand these clauses in detail:
Suppose we have two fields in the dataset and we want to create a calculated metric to categorize the marks using a CASE statement. Let’s call this column as Grade.
The required logic to calculate the grade is as mentioned below:
- Marks above and equal to 75 will get A grade
- Marks above or equal 50 but below 75 will get B grade
- Marks above or equal 25 but below 50 will get C grade
- Marks above or equal to 0 but below 25 will get D grade
Let’s write an SQL as an example, for fetching the same and call this table as Stu_Marks
Code:
Select
ID,
Marks,
CASE
WHEN Marks > =75 then 'A'
WHEN Marks > =50 then 'B'
WHEN Marks > =25 then 'C'
ELSE 'D'
END as Grade
from Stu_Marks
Here using the above case statement we have created another column called Grades, which has been derived based on the conditional statements over the column named “Marks”
- If it follows the conditional mentioned the first WHEN clausethen the Grade is set to ‘A’ for those columns.
- If it follows the conditional mentioned the second WHEN clausethen the Grade is set to ‘B’ for those columns.
- If it follows the conditional mentioned the third WHEN clausethen the Grade is set to ‘C’ for those columns.
- Else, if none of the above conditional is met then the Grade is set to ‘D’ due to the ELSE Clause
Output: The resultant output of this select clause will be as mentioned below: The name of the new column created using the CASE statement will be ‘Grade’ as we have aliased the column using the as clause after the END statement.
How to use the CASE Statement in Teradata?
When we need to create a calculated field based on some conditional, Teradata’s CASE Statement can be used. Here we specify different conditionals under WHEN Statement, based on what the result set is derived. If in case no conditional is met, the result set in else conditional is set. The Syntax of Teradata’s CASE statement is as mentioned below:
Syntax:
CASE
WHEN <Conditional_1> THEN <Result_1>
WHEN <Conditional_2> THEN <Result_2>
WHEN <Conditional_3> THEN <Result_3>
WHEN <Conditional_4> THEN <Result_4>
WHEN <Conditional_5> THEN <Result_5>
WHEN <Conditional_6> THEN <Result_6>
WHEN <Conditional_7> THEN <Result_7>
WHEN <Conditional_8> THEN <Result_8>
WHEN <Conditional_9> THEN <Result_9>
WHEN <Conditional_10> THEN <Result_10>
ELSE <Result_11>
END
- Conditionals may or may not be based on the existing columns
- Many Logical Operators can be used within these conditionals such as Less Than, Greater than, etc
Explanation:
- Equal to(==): In order to check for the equality of two variables an “equal to” operator is used. It consists of two equal to mathematical signs and that’s what makes it different from the assignment operator. For example : x == p
- Not Equal to(!=): In order to check for the inequality of two variables, a “Not equal to” operator is used. It consists of a not and an equal to mathematical sign. For example : x != p
- Less Than( < ): In order to check for the inequality of two variables, particularly if variable1 is less than variable2, a “Less Than” operator is used. It consists of a less than mathematical sign. For example: x < p
- Less Than Equal to( <= ): In order to check for the inequality of two variables, particularly if variable1 is less than equal to variable2, a “Less Than equal to” operator is used. It consists of a less than and an equal to mathematical sign. For example: x <= p
- Greater Than( > ): In order to check for the inequality of two variables, particularly if variable1 is greater than variable2, a “Greater Than” operator is used. It consists of a greater than mathematical sign. For example: x > p
- Greater Than Equal to( >= ): In order to check for the inequality of two variables, particularly if variable1 is greater than equal to variable2, a “Greater Than equal to” operator is used. It consists of a greater than and an equal to mathematical sign. For example: x >= p
Conclusion
CASE statement is used to generate new columns based on the conditionals using the existing ones. Multiple WHEN clause can exist within a CASE statement If none of the conditional is met under WHEN clauses, then the result under ELSE clause is set to END statement is compulsory, else a syntax error will be thrown
Recommended Articles
This is a guide to Teradata CASE Statement. Here we discuss the introduction to Teradata CASE Statement, how to use CASE statements with examples to implement. You can also go through our other related articles to learn more –
