• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Sql » Functions » SQL: DECODE Function
Next →
← Prev

SQL: DECODE Function

By Dinesh Thakur

The DECODE function can be thought of as an inline IF statement. DECODE takes three or more expressions as arguments. Each expression can be a column, a literal, a function, or even a subquery. Let’s look at a simple example using DECODE:

SELECT lname,DECODE(manager_emp_id, NULL, ‘HEAD HONCHO’, ‘WORKER BEE’) emp_type FROM employee;

LNAME         EMP_TYPE

SMITH            WORKER  BEE

ALLEN            WORKER  BEE

WARD            WORKER  BEE

JONES            WORKER  BEE

MARTIN         WORKER  BEE

BLAKE           WORKER  BEE

CLARK           WORKER  BEE

SCOTT            WORKER  BEE

KING              HEAD  HONCHO

TURNER         WORKER  BEE

ADAMS          WORKER  BEE

JAMES            WORKER  BEE

FORD              WORKER  BEE

MILLER          WORKER  BEE

In this example, the first expression is a column, the second is NULL, and the third and fourth expressions are character literals. The intent is to determine whether each employee has a manager by checking whether an employee’s manager_emp_id column is NULL. The DECODE function in this example compares each row’s manager_emp_id column (the first expression) to NULL (the second expression). If the result of the comparison is true, DECODE returns ‘HEAD HONCHO’ (the third expression); otherwise, ‘WORKER  BEE’ (the last expression) is returned.

Since the DECODE function compares two expressions and returns one of two expressions to the caller, it is important that the expression types are identical or that they can at least be translated to be the same type. This example works because E1 can be compared to E2, and E3 and E4 have the same type. If this were not the case, Oracle would raise an exception, as illustrated by the following example:

SELECT lname,DECODE(manager_emp_id, SYSDATE, ‘HEAD HONCHO’, ‘WORKER BEE’) emp_type FROM employee;
ERROR at line 1:
ORA–00932: inconsistent datatypes: expected DATE got NUMBER

Since the manager_emp_id column, which is numeric, cannot be converted to a DATE type, the Oracle server cannot perform the comparison and must throw an exception. The same exception would be thrown if the two return expressions (E3 and E4) did not have comparable types.

The previous example demonstrates the use of a DECODE function with the minimum number of parameters (four). The next example demonstrates how additional sets of parameters may be utilized for more complex logic:

SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier, DECODE(p.status,‘INSTOCK’, ‘In Stock’,‘DISC’, ‘Discontinued’,‘BACKORD’, ‘Backordered’,‘ENROUTE’, ‘Arriving Shortly’,‘UNAVAIL’, ‘No Shipment Scheduled’, ‘Unknown’) part_statusFROM part p INNER JOIN supplier sON p.supplier_id = s.supplier_id;

PART_NBR   PART_NAME                   SUPPLIER              PART_STATUS

AI5-4557         Acme  Part  AI5-4557     Acme  Industries         In  Stock

TZ50828         Tilton  Part  TZ50828       Tilton  Enterprises       In  Stock

EI-T5-001        Eastern  Part  EI-T5-001 Eastern  Importers      In  Stock

This example compares the value of a part’s status column to each of five values, and, if a match is found, returns the corresponding string. If a match is not found, then the string  ‘Unknown’ is returned. Although the 12 parameters in this example are a great deal more than the 4 parameters of the earlier example, we are still a long way from the maximum allowable parameters, which is 255.

You’ll also like:

  1. SQL NVL Function
  2. SQL DATEPART() Function
  3. SQL GETDATE() Function
  4. SQL CAST() Function
  5. SQL: COUNT Function
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

SQL Tutorials

SQL Tutorials

  • SQL - Home
  • SQL - Select
  • SQL - Create
  • SQL - View
  • SQL - Sub Queries
  • SQL - Update
  • SQL - Delete
  • SQL - Order By
  • SQL - Select Distinct
  • SQL - Group By
  • SQL - Where Clause
  • SQL - Select Into
  • SQL - Insert Into
  • SQL - Sequence
  • SQL - Constraints
  • SQL - Alter
  • SQL - Date
  • SQL - Foreign Key
  • SQL - Like Operator
  • SQL - CHECK Constraint
  • SQL - Exists Operator
  • SQL - Drop Table
  • SQL - Alias Syntax
  • SQL - Primary Key
  • SQL - Not Null
  • SQL - Union Operator
  • SQL - Unique Constraint
  • SQL - Between Operator
  • SQL - Having Clause
  • SQL - Isnull() Function
  • SQL - IN Operator
  • SQL - Default Constraint
  • SQL - Minus Operator
  • SQL - Intersect Operator
  • SQL - Triggers
  • SQL - Cursors

Advanced SQL

  • SQL - Joins
  • SQL - Index
  • SQL - Self Join
  • SQL - Outer Join
  • SQL - Join Types
  • SQL - Cross Join
  • SQL - Left Outer Join
  • SQL - Right Join
  • SQL - Drop Index
  • SQL - Inner Join
  • SQL - Datediff() Function
  • SQL - NVL Function
  • SQL - Decode Function
  • SQL - Datepart() Function
  • SQL - Count Function
  • SQL - Getdate() Function
  • SQL - Cast() Function
  • SQL - Round() Function

Other Links

  • SQL - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2025. All Rights Reserved.

APPLY FOR ONLINE JOB IN BIGGEST CRYPTO COMPANIES
APPLY NOW