CASE Statement in Teradata

Teradata CASE statement provides the flexibility to fetch alternate values for a column base on the condition specified in the expression.  Not getting? Ok, let me explain with a simple example.

Teradata CASE Statement Example

Consider the below customer table.

Now, you can write some CASE statement on marital_status as below.

SELECT   
 cust_id,
 income,
 age,
 years_with_bank,
 nbr_children, gender,
 CASE WHEN marital_status=1 THEN 'Single'
      WHEN marital_status=2 THEN 'Married'
 ELSE 'Not Sure'
 END AS marital_status
 FROM teradatapoint.customer;

If the marital_status is 1, it will return as Single and if the marital_status is 2, then this query will return as Married.

Below is the output of the above query-

*** Query completed. 6 rows found. 7 columns returned.
  *** Total elapsed time was 1 second.

 cust_id       income     age   years_with_bank  nbr_children  gender  marital_status
 -----------  ----------- ------  ------------  ------------  ------  --------------
 1362487         6605      71                1             0  M       Married
 1362956            0      10                4             0  F       Single
 1362752        47668      54                3             0  F       Single
 1363221            0      10                4             0  F       Single
 1362813        15684      53                3             2  F       Married
 1362609         1929      79                8             0  F       Married

We can write the above query in some other way as well.

SELECT    cust_id, 
 income, 
 age, 
 years_with_bank, 
 nbr_children, 
 gender,
 CASE marital_status WHEN 1 THEN 'Single'                    
 WHEN 2 THEN 'Married
 'ELSE 'Not Sure'END AS marital_status
 FROM teradatapoint.customer;

COALESCE in Teradata

COALESCE in Teradata is used for NULL handling. The COALESCE returns the first NOT NULL value encountered in the provided list. Otherwise it returns NULL if all the arguments in the expression list evaluate to NULL. 

COALESCE Function Syntax 

The basic syntax of COALESCE function is as below-

COALESCE(expression1, expression2 [,expression list])

COALESCE Function Example 

Consider the below employee table.

Now, you can prioritize which phone number to select using COALESCE function as below.

SELECT
 emp_no,
 first_name,
 last_name,
 COALESCE(ph_no1,home_no,ph_no2,'Phone# not available') AS Phone_No
 FROM teradatapoint.employee;

The above query will search for ph_no1 first. If that is NULL , it will search for home_no and ph_no2 respectively. If none of the argument is returning not null value, it will return the default value.

*** Query completed. 3 rows found. 4 columns returned.
  *** Total elapsed time was 1 second.

 

     emp_no  first_name  last_name   Phone_No
 -----------  ----------  ----------  --------------------
         345  Amal        Roy         Phone# not available
         123  Alex        Martin                7209756747
         134  Sager       Sharma                  34567548

Also Read:-

• Teradata SELECT Statement
• Teradata Logical operator
• Teradata SET operators
• Teradata DATE/TIME Function
• Teradata SAMPLE Function