Netezza Case Statement and its Usage with Examples

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

The Netezza CASE statement chooses value from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential available values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE.

Netezza Case Statement Syntax

Searched form:

CASE
 WHEN <search-condition-1> THEN <result-1>
 WHEN <search-condition-2> THEN <result-2>
...
 WHEN <search-condition-n> THEN <result-n>
 ELSE <default-result>
END

Search conditions can be arbitrarily complex and results can be expressions.

Value form:

CASE <test-value>
 WHEN <comparand-value-1> THEN <result-1>
 WHEN <comparand-value-2> THEN <result-2>
...
 WHEN <comparand-value-n> THEN <result-n>
 ELSE <default-result>
END

Test values, compare values, and results can be expressions.

Netezza Case Statement Examples

Below is the table that we use in testing case statement:

TRAINING.ADMIN(ADMIN)=> select * from STUDENTS; 
 CLASS | SECTION | PASS_FAIL | COUNT 
--------+---------+-----------+------- 
 CLASS1 | A | FAIL | 123 
 CLASS1 | A | PASS | 321 
 CLASS2 | B | FAIL | 333 
 CLASS2 | B | PASS | 543 
(4 rows)

Netezza CASE Searched form Example

SELECT 
CASE 
 WHEN PASS_FAIL = 'FAIL' then 'Student is Failed in Exam!'
 WHEN PASS_FAIL = 'PASS' then 'Student is passed in Exam!'
 WHEN PASS_FAIL = 'AB' then 'Student did not attend Exam!'
 ELSE 'Search Condition not Found!'
END
FROM STUDENTS;
TRAINING.ADMIN(ADMIN)-> FROM STUDENTS; 
 CASE 
---------------------------- 
 Student is Failed in Exam! 
 Student is passed in Exam! 
 Student is Failed in Exam! 
 Student is passed in Exam! 
(4 rows)

Netezza CASE Value form Example

SELECT 
CASE PASS_FAIL
 WHEN 'FAIL' THEN 'Student is Failed in Exam!'
 WHEN 'PASS' THEN 'Student is Passed in Exam!'
 WHEN 'AB' THEN 'Student did not attend Exam!'
 ELSE 'Value not Found!'
END
FROM STUDENTS;

TRAINING.ADMIN(ADMIN)-> FROM STUDENTS; 
 CASE 
---------------------------- 
 Student is Failed in Exam! 
 Student is Passed in Exam! 
 Student is Failed in Exam! 
 Student is Passed in Exam! 
(4 rows) 
TRAINING.ADMIN(ADMIN)=>

Read:

Tags: , ,