Creating an artificial sort order using the Oracle DECODE function

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>