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

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

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

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

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