Teradata case statement

In Teradata using case statement, you can specify an alternative value for a conditional expression based on the equality comparison that evaluates to true. Basically you represent some data based on different conditions.

For example, you could code employee status as 1 or 2, meaning full-time or part-time, respectively. For efficiency, the system stores the numeric code but prints or displays the appropriate textual description in reports. This storage and conversion is managed by Teradata Database.

In addition, CASE permits applications to generate nulls based on information derived from the database, again without host program intervention. Conversely, CASE can be used to convert a null into a value.

Example of Case statement in Teradata

The following example uses a Valued CASE expression to calculate the fraction of cost in the total cost of inventory represented by parts of type ‘1’:

   SELECT SUM(CASE part
               WHEN '1' 
               THEN cost
               ELSE 0
              END
             )/SUM(cost)
   FROM t;

Example of Case statement in Teradata

A CASE expression can be used in place of any value-expression.

   SELECT *
   FROM t
   WHERE x = CASE y
              WHEN 2
              THEN 1001
              WHEN 5
              THEN 1002
             END;

Example
The following example shows how to combine a CASE expression with a concatenation operator:

   SELECT prodID, CASE prodSTATUS 
                    WHEN 1 
                    THEN 'SENT' 
                    ELSE 'BACK ORDER' 
                    END || ' STATUS'
   FROM t1;

Example:

The following example shows how using a CASE expression can result in significantly enhanced performance by eliminating multiple passes over the data. Without using CASE, you would have to perform multiple queries for each region and then consolidate the answers to the individual queries in a final report.

   SELECT SalesMonth, SUM(CASE 
                           WHEN Region='NE' 
                           THEN Revenue 
                           ELSE 0 
                          END),
                      SUM(CASE 
                           WHEN Region='NW' 
                           THEN Revenue 
                           ELSE 0 
                          END),
                      SUM(CASE 
                           WHEN Region LIKE 'N%' 
                           THEN Revenue 
                           ELSE 0 
                          END)
   AS NorthernExposure, NorthernExposure/SUM(Revenue),
   SUM(Revenue)
   FROM Sales
   GROUP BY SalesMonth;