CASE and COALESCE Statement in Teradata



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.

cust_id income age years_with_bank nbr_children gender marital_status
1,362,487 6,605 71 1 0 M 2
1,362,956 0 10 4 0 F 1
1,362,752 47,668 54 3 0 F 1
1,363,221 0 10 4 0 F 1
1,362,813 15,684 53 3 2 F 2
1,362,609 1,929 79 8 0 F 2

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;

 

The COALESCE Function

The COALESCE function returns NULL if all the arguments in the expression list evaluate to NULL. Otherwise, it returns the first not null argument in the list.

 

COALESCE Function Syntax 

COALESCE(expression1, expression2 [,expression list])

 

COALESCE Function Example 

Consider the below employee table.

emp_no first_name last_name home_no ph_no1 ph_no2
345 Amal Roy ? ? ?
123 Alex Martin 45637887 7209756747 ?
134 Sager Sharma 34567548 ? ?

 

Now, you can prioritize which phone number to select using COLAESCE 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