The DECODE function is an Oracle-specific SQL tool that can be used to change one value into a different value. Normally the function is used in the expression clause of the SELECT statement. However, this function can also be used to dramatically change the sorting of the query's data.
Records returned by a SELECT statement are normally sorted by the value(s) from one or more of the expressions displayed in the expression clause. However, on some occasions it is necessary to sort the query's records in an artificial manner, something other than ascending or descending of a displayed value. The DECODE function will allow you to do this.
The first step is to understand the structure of the DECODE function. The following is a syntax template:
DECODE (returned value, value, replacement value,
value, replacement value, catch-all replacement value)
The first parameter is the target column or expression. It represents the value that is to be DECODEd. This parameter is followed by a series of dual parameters. Each set of parameters is a possible column value and its replacement value. The last parameter is a catch-all value that is used when a returned value is encountered that does not have a value in the DECODE values.
Listing 1 (below) illustrates the DECODE function in action.
The function is used to change the fk_department value into the full department name. The last or catch-all parameter value for the function is 'UNKNOWN'.
Now that you understand the basic layout of the DECODE function, we can see how the function can be used in a query's Order By clause. The default record sorts are ascending and descending. If you can only display one value, but use a different (DECODEd) value for sorting, it would be possible to arrange records in unique manners. The DECODE function allows us to do this.
Listing 2 (below) illustrates this technique. The query is used for a report for the RST Company. The company has five departments. For a particular report, it is necessary to sort the results in the order the departments were initiated. Since the Initiation Date does not exist as a database value, an artificial sort is needed. The query will need to sort the records causing the Treasury department to be displayed first followed by Welfare, Census, Interior, and Political Science.
The DECODE function is used to achieve this sorting. Notice that the TRF or Treasury Department is assigned a value of 1. The other departments are ranked accordingly. The result is a sort order that matches the order the departments were initiated.
While this technique is not commonly used, sometimes it is very necessary. One of my regular uses is in the SELECT statement that populates Developer 6i Hierarchical items. Records in a Hierarchical item must be ranked. This technique allows me to properly rank the records.
About the Author
John Palinski has been an Oracle developer and project manager for the last fourteen years. He developed one of one of the first relational database work management systems in the country. Mr. Palinski also teaches his own Oracle courses at Iowa Western Community College and the University of Nebraska at Omaha. Mr. Palinski is the author of the "Oracle Database Construction Kit" published by QUE and provides custom Oracle training worldwide through his consulting business, Realistic Software Training.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our new Ask the Experts feature: Our Oracle gurus are waiting to answer your toughest questions.
Listing 1. Using the DECODE function to change the value of the Fk_department column.
SQL> select fk_department, 2 decode(fk_department, 'POL', 'POLITICAL SCIENCE', 3 'WEL', 'WELFARE', 'UNKNOWN'), last_name, first_name 4 from employee 5 ; FK_D DECODE(FK_DEPARTM LAST_NAME FIRST_NAME ---- ----------------- --------------- --------------- WEL WELFARE HOOVER HERBERT POL POLITICAL SCIENCE WILSON WOODROW WEL WELFARE TAFT WILLIAM INT UNKNOWN ROOSEVELT THEODORE WEL WELFARE ANTHONY SUSANNE WEL WELFARE ROOSEVELT ELEANOR INT UNKNOWN COOLIDGE CALVIN INT UNKNOWN MILLER KEVIN POL POLITICAL SCIENCE DWORCZAK ALICE POL POLITICAL SCIENCE JOHNSON LYNDON WEL WELFARE REAGAN RONALD INT UNKNOWN BUSH GEORGE POL POLITICAL SCIENCE JOHNSON ANDREW POL POLITICAL SCIENCE CLINTON WILLIAM WEL WELFARE CARTER JIMMY INT UNKNOWN FORD GERALD POL POLITICAL SCIENCE NIXON RICHARD POL POLITICAL SCIENCE KENNEDY JOHN INT UNKNOWN EISENHOWER DWIGHT INT UNKNOWN TRUMAN HAROLD POL POLITICAL SCIENCE ROOSEVELT FRANKLIN 21 rows selected. SQL>
Listing 2. Using the DECODE function to change the sorting of records.
SQL> select department, department_name 2 from department 4 order by decode(department, 'POL', 5, 'TRF', 1, 5 'WEL', 2, 'CEN', 3, 4 'INT', 4); DEPA DEPARTMENT_NAME ---- --------------- TRF TRESURY DEPAR WEL WELFARE BUREAU CEN CENSUS DEPT INT INTERIOR DESIGN POL POLITICAL SCIEN SQL>