Thursday, 13 June 2013

Conditional Expression in SQL

  • Provide the use of IF-THEN-ELSE logic within a SQL statement.
  • Use two methods:
    • CASE expression.
    • DECODE function.

The CASE Expression

  • Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement.

CASE expr WHEN comparison_expr1 THEN return_expr1
                    [ WHEN comparison _expr2 THEN return_expr2
                     WHEN comparison_exprn THEN return_exprn
                     ELSE else_expr ]

  • In a simple CASE expression, Oracle searches for the first WHEN --- THEN pair for which expr is equal to comparison_expr and returns return_expr. 
  • If none of the WHEN --- THEN pairs meet this condition, and ELSE clause exists, then Oracle returns else_expr.
  • Otherwise, Oracle returns null.
  • You can't specify the literal NULL for all the return_exprs and the else_expr.

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:


DECODE(col|expression, search1, result1

  • The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. 
  • The DECODE function decodes expression after comparing it to each search value.
  • If the expression is the same as search, result is returned.
  • If the default value is omitted, a null value is returned where a search value does not match any of the result value.

No comments:

Post a Comment