I was having fun with the DECODE command while playing around with Oracle 10g. For the note, the description of DECODE on Oracle’s website is more than a little bit like mud. I guess there is no easy way to explain a computer method, but I found this non-useful.

So, here we go. Say you have employees that you need to divvy up into categories by hours worked, and you want Oracle (or whatever) to automatically do it. You could use the following code:

SELECT EMPLOYEE.LNAME, EMPLOYEE.FNAME, WORKS_ON.HOURS,

DECODE(TRUNC(HOURS/10), 0, ‘D’,  1, ‘C’, 2, ‘B’, 3, ‘A’) CODE

FROM WORKS_ON, EMPLOYEE

WHERE EMPLOYEE.SSN = WORKS_ON.ESSN

So, what you have here is the DECODE in all the glory. It takes employee hours worked during the span (probably weekly) and divides the hours by 10 (truncated, in order to round down). This gives a result between 0-3.

DECODE then assigns a CODE using this method; the final result looks something similar to the following:

You could use the DECODE for grading, sales, or pretty much anything that requires coded output. There is also a nifty little trick with DECODE that I discovered:

SELECT EMPLOYEE.LNAME, EMPLOYEE.FNAME, PROJECT.PNAME, WORKS_ON.HOURS,

DECODE(TRUNC(HOURS/10), 0, ‘D’) CODE_D,

DECODE(TRUNC(HOURS/10), 1, ‘C’) CODE_C,

DECODE(TRUNC(HOURS/10), 2, ‘B’) CODE_B,

DECODE(TRUNC(HOURS/10), 3, ‘A’) CODE_A

FROM WORKS_ON, EMPLOYEE

WHERE EMPLOYEE.SSN = WORKS_ON.ESSN

This DECODE yields a slightly different spin by coding in a matrix:

Nicely divided into a matrix, for all those reporting needs.

DECODE can be made a little less obfuscated by practical examples. As one friend stated on Facebook: “I learned more math in Physics or Statistics than I ever did in Calculus.” Applied mathematics and applied coding is still the best way to learn.

Related articles

• Oracle 10g: Some Interesting Bits (velluminformation.com)