Sample Code for Oracle 10g: DECODE, applied

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:

Smith John 32.5 A
Smith Grace 7.5 D
Ramesh Nariyan 40 A
English Joyce 20 B

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:

LNAME

FNAME

HOURS

CODE_D

CODE_C

CODE_B

CODE_A

Smith John 32.5  – A
Smith John 7.5 D
Ramesh Nariyan 40  – A
English Joyce 20  – B

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.

Advertisements
This entry was posted in Information Technology, Resource-a-rama and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s