103. DECODE - llighter/database GitHub Wiki

DECODE Database SQL Reference

Syntax

Purpose

  • DECODE compares expr to each search value one by one.
  • If expr is equal to a search, then Oracle Database returns the corresponding result`.
  • If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.
  • The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.
  • The search, result, and default values can be derived from expressions.
  • Oracle Database uses short-circuit evaluation. :

ν•˜λ‚˜ 찾으면 더 이상 λΉ„κ΅ν•˜μ§€ μ•ŠλŠ”λ‹€.

  • In a DECODE function, Oracle considers two nulls to be equivalent.
  • The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

Examples

SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake', 
                             2, 'San Francisco', 
                             3, 'New Jersey', 
                             4, 'Seattle',
                                'Non domestic') 
       "Location of inventory" FROM inventories
       WHERE product_id < 1775;