Social Icons

CASE Function

TeradataWiki-Teradata case function
Teradata CASE function also is same like as Oracle case.
  • CASE allows for conditional processing of returned rows.
  • CASE returns a single result for each row processed.
  • Each row is evaluated against each WHEN clause.
  • First match returns a result for that row.
  • If no match, ELSE result is produced for that row.
Syntax:
CASE value-expr WHEN  expr1 THEN result1
WHEN expr2 THEN result2
:
ELSE resultn END

Example:
Calculate the fraction of the total salary of all employees represented by the salaries of Dept. 401.
SELECT SUM(
CASE department_number
WHEN 401 THEN salary_amount
ELSE 0
END) / SUM(salary_amount)
FROM employee;

Result:
(Sum()/Sum(salary_amount))
-------------------------------------------------------------
2.22834717118098E-001

Example 2:
Get the total salaries for departments 401 and 501.
SELECT CAST (SUM(
CASE department_number
WHEN 401 THEN salary_amount WHEN 501 THEN salary_amount
ELSE 0
END) / AS NUMERIC (9,2))
AS total_sals_401_501
FROM EMPLOYEE;

Result:
total sals 401 501
-------------------
4457000.00

Searched CASE Statement

CASE WHEN  condition1 THEN value-expr1
            WHEN  condition2 THEN value-expr2
                 :
                 :
                 :
ELSE value-expr END

Example:
SELECT last_name, 
CASE  WHEN  salary_amount < 30000  THEN 'Under $30K'
           WHEN  salary_amount < 40000  THEN 'Under $40K'
           WHEN  salary_amount < 50000   THEN 'Under $50K'
ELSE
’< = $50K'
END
FROM employee
ORDER BY salary_amount;

Result:
last_name        
--------------       ------------------------
Phillips             Under $30K
Crane               Under $30K
Hoover             Under $30K
Rabbit              Under $30K
Kanieski           Under $30K
Stein Under      $30K
Lombardo Under $40K
Ryan                Under $40K
Machado          Under $40K
Short               Under $40K
Johnson           Under $40K
Trader             Under $40K
Hopkins           Under $40K
Morrissey         Under $40K
Charles            Under $40K
Brown              Under $50K
Brown              Under $50K
Rogers              Under $50K
Villegas            Under $50K
Daly Over        $50K
Wilson            Over  $50K
Ratzlaff          Over  $50K
Rogers            Over  $50K
Kubic             Over  $50K
Runyon          Over  $50K
Trainer           Over  $50K