EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Teradata Tutorial Teradata CASE Statement

Teradata CASE Statement

By Priya PedamkarPriya Pedamkar

Teradata CASE Statement

Introduction to Teradata CASE Statement

Teradata’s CASE statement is used to check for various conditions and in a sequential manner 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 CASE Statement’s result not matching any of the mentioned cases will be NULL.

If you have many multiple conditionals to check and need to apply the first conditional in sequence, use the CASE statements.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 WHEN clause’s conditionals 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 to understand these clauses in detail:

Teradata CASE Statement - 1

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 Grade.

The required logic to calculate the grade is as mentioned below:

  • Marks above and equal to 75 will get an A grade.
  • Marks above or equal 50 but below 75 will get a B grade.
  • Marks above or equal 25 but below 50 will get a C grade.
  • Marks above or equal to 0 but below 25 will get a 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

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.

Teradata CASE Statement - 2

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 the 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(==): To check for the equality of two variables, an “equal to” operator is used. It consists of two equal mathematical signs, which makes it different from the assignment operator. For example : x == p
  • Not Equal to(!=): 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( < ): 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( <= ): 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( > ): 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( >= ): 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

The 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 ELSE clause’s result is set to END statement is compulsory; else, a syntax error will be thrown.

Recommended Articles

We hope that this EDUCBA information on “Teradata CASE Statement” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Career In Teradata
  2. What is Teradata?
  3. Teradata Interview Questions
  4. Case Statement in Tableau
  5. Guide to Teradata Partition by
  6. Learn the Components of Teradata Architecture
  7. What is the Primary Index in Teradata?
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more