Tip

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

    Requires Free Membership to View

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


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>

This was first published in January 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.