Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Built-In Functions: CASE Jump to:  
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)

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;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 61 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?