CoderZone.org

Category: >> Oracle PL/SQL >> Built-In Functions: CASE Bookmark and Share

<< lastnext >>

Snippet Name: Built-In Functions: CASE

Description: Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the case statement is:


'expression' is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

'condition_1' to 'condition_n' must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.

'result_1' to 'result_n' must all be the same datatype. This is the value returned once a condition is found to be true.

Also see:
» FUNCTIONS: Deterministic
» FUNCTIONS: Nested Functions
» FUNCTIONS: IF statement
» FUNCTIONS: date/time
» FUNCTIONS: Sample functions
» FUNCTIONS: drop
» FUNCTIONS: Recompile
» FUNCTIONS: DEBUG mode
» FUNCTIONS: IN OUT parameter
» FUNCTIONS: with output parameters
» FUNCTIONS: with parameters
» FUNCTIONS: without parameters
» FUNCTIONS: Create function
» FUNCTIONS: special restrictions
» FUNCTIONS: System Privileges
» IN Function
» Built-In Functions: DECODE
» SUBST and INSTR together
» INSTR (InString)
» SUBSTR (SubString)
» String Functions: REVERSE
» String Functions: LENGTH
» String Functions: INSTR
» String Functions: CONCAT
» String Functions: CHAR
» String Functions: INITCAP
» String Functions: LOWER
» String Functions: UPPER
» Date Functions: NUMTOYMINTERVAL
» Date Functions: NUMTODSINTERVAL

Comment: (none)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 12th, 2009

SELECT size_field, 
  CASE WHEN (size_field BETWEEN 1 AND 10) THEN 'One'
       WHEN (size_field BETWEEN 11 AND 100) THEN 'Big'
  ELSE 'Bigger'
  END
FROM test_table;
 
 
-- You can use the case statement in an SQL statement as 
-- follows: (includes the expression clause)
 
    SELECT table_name,
    CASE owner
      WHEN 'SYS' THEN 'The owner is SYS'
      WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
      ELSE 'The owner is another value'
    END
 
 
-- you can write the SQL statement using the case statement 
-- like this: (omits the expression clause)
 
    SELECT table_name,
    CASE
      WHEN owner='SYS' THEN 'The owner is SYS'
      WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
      ELSE 'The owner is another value'
    END
    FROM all_tables;
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 8