| Print Open PDF Version of Online Help

#### Case Statements Case statements are building blocks for creating conditional expressions that can produce different results for different column values. ### ### Case (Switch) This form of the Case statement is also referred to as the CASE (Lookup) form. The value of expression1 is examined, then the WHEN expressions. If expression1 matches any WHEN expression, it assigns the value in the corresponding THEN expression. If expression1 matches an expression in more than one WHEN clause, only the expression following the first match is assigned. If none of the WHEN expressions match, it assigns the value specified in the ELSE expression. If no ELSE expression is specified, it assigns the value NULL. Refer also to the Case (If) statement. Syntax: CASE expression1 WHEN expression THEN expression {WHEN expression... THEN expression...} ELSE expression END Where: CASE Starts the CASE statement. Must be followed by an expression and one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword. WHEN Specifies the condition to be satisfied. THEN Specifies the value to assign if the corresponding WHEN expression is satisfied. ELSE Specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed. END Ends the CASE statement. Example CASE Score-par WHEN -5 THEN 'Birdie on Par 6' WHEN -4 THEN 'Must be Tiger' WHEN -3 THEN 'Three under par' WHEN -2 THEN 'Two under par' WHEN -1 THEN 'Birdie' WHEN 0 THEN 'Par' WHEN 1 THEN 'Bogey' WHEN 2 THEN 'Double Bogey' ELSE 'Triple Bogey or Worse' END In the above example, the WHEN statements must reflect a strict equality; a WHEN condition of WHEN < 0 THEN 'Under Par' is illegal because comparison operators are not allowed. ### ### Case (If) This form of the Case statement evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression. If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, it assigns the value NULL. Refer also to the Case (Switch) statement. Syntax: CASE WHEN request_condition1 THEN expression1 {WHEN request_condition2 THEN expression2} {WHEN request_condition... THEN expression...} ELSE expression END Where: CASE Starts the CASE statement. Must be followed by one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword. WHEN Specifies the condition to be satisfied. THEN The value to assign if the corresponding WHEN expression is satisfied. ELSE Specifies the value if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed. END Ends the CASE statement. Example CASE WHEN score-par < 0 THEN 'Under Par' WHEN score-par = 0 THEN 'Par' WHEN score-par = 1 THEN 'Bogie' WHEN score-par = 2 THEN 'Double Bogey' ELSE 'Triple Bogey or Worse' END Unlike the Switch form of the CASE statement, the WHEN statements in the If form allow comparison operators; a WHEN condition of WHEN < 0 THEN 'Under Par' is legal.

Published August 2022 | Copyright © 2005, 2022, Oracle and/or its affiliates. Legal Notices.