How to write nested Case statement in Teradata?
Contents
Case Statement in Teradata
CASE statement is a conditional expression that used to evaluate the conditions or perform the equality comparisons against column values in Teradata. It goes through each condition and returns the value when the condition is met( Like an IF-THEN-ELSE-Statement).
If no condition are TRUE in Case statement, It returns the value defined by an optional ELSE clause, or if omitted, NULL.
Syntax for Case Statement
|
1 2 3 4 5 6 7 8 |
CASE WHEN <value_expression_1> THEN result_1 WHEN <value_expression_2> THEN result_2 ELSE result_n END |
Nested Case Statement in Teradata
Teradata allows to add one case statement inside the another case statement. The nested case statement helps to validate the multiple criteria under one WHEN condition.The inner case statement requires their own END statement.
Syntax for Nested Case Statement
|
1 2 3 4 5 6 7 8 9 10 11 |
CASE WHEN <value_expression_1> THEN CASE WHEN <inner_value_expression_1> THEN <inner_result_1> WHEN <inner_value_expression_2> THEN <inner_result_2> ELSE <inner_result_n> END WHEN <value_expression_2> THEN result_2 ELSE result_n END |
Examples for Case and Nested case statement
Lets see the case and nested case statements with examples in Teradata
The Government giving scholarship for the student’s higher education. The scholarship amount will vary depends on the education streams and the fees amount. Here we are going to use the case and nested case statements to calculate the scholarship amount.
Example : Case statement
- If the Higher studies is Medical, scholarship amount is 50000
- If the Higher studies is Engineering, scholarship amount is 40000
- For other Higher studies, scholarship amount is 25000
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT Student_id, Higher_Studies, College_Fees, Address, CASE WHEN Higher_Studies = 'Medical' THEN 50000 WHEN Higher_Studies = 'Engineering' THEN 40000 ELSE 25000 END as Scholarship FROM Student_DB.Education_Fees; |
Output
Example : Nested Case statement
- If the Higher studies is Medical, scholarship amount will vary depends on the College fees as below
- If the College fees is less than 200000, scholarship amount is 50000
- If the College fees is less than 300000 , scholarship amount is 75000
- If the College fees is more than 300000, scholarship amount is 100000
- If the Higher studies is Engineering, scholarship amount will vary as below
- If the College fees is less than 150000 , scholarship amount is 40000
- If the College fees is less than 250000 , scholarship amount is 60000
- If the College fees is more than 250000 , scholarship amount is 80000
- For other higher studies, scholarship amount will be 25000
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT Student_id, Higher_Studies, College_Fees, Address, Case WHEN Higher_Studies = 'Medical' THEN CASE WHEN College_Fees < 200000 THEN 50000 WHEN College_Fees < 300000 THEN 75000 ELSE 100000 END WHEN Higher_Studies = 'Engineering' THEN CASE WHEN College_Fees < 150000 THEN 40000 WHEN College_Fees < 250000 THEN 60000 ELSE 80000 END ELSE 25000 END as Scholarship FROM Student_DB.Education_Fees; |
Output
Recommended Articles
- How to use Qualify row number in Teradata?
- REGEXP_SUBSTR function in Teradata with examples
- REGEXP_REPLACE function in Teradata with examples
- Pivot function in Teradata with examples
- Substring function in Teradata with examples
