Problem solve Get help with specific problems with your technologies, process and projects.

Using DECODE to display invoice balances by due date

I want to write a query using the DECODE function to display the invoice balance of patients in categories of due dates. For example, due this month, thirty days past due, sixty days past due, ninety days past due. I will really apprieciate your help.

You are on the right track in using DECODE for this functionality. Or, you could use Oracle 9i's new CASE function in your SQL statement. But I'll still show you how to do it using DECODE.

For my example, I'm going to query DBA_USERS and find out which users were created this month, within the last 30 days, within the last 60 days, within the last 90 days, and more than 90 days ago. From my example below, you should be able to easily adapt it to your situation:

ORA9I SQL> SELECT username,created,
  2  DECODE(TRUNC((SYSDATE-created)/30),0,'Current Month',
  3  1,'Last Month',
  4  2,'Sixty Days Ago',
  5  3,'Ninety Days Ago',
  6  'More than 90 days')  AS time_frame
  7  FROM dba_users;

---------- --------- -----------------
SYS        26-FEB-02 More than 90 days
SYSTEM     26-FEB-02 More than 90 days
AURORA$JIS 26-FEB-02 More than 90 days

AURORA$ORB 26-FEB-02 More than 90 days

DBSNMP     19-FEB-03 Sixty Days Ago
DBAMON     17-OCT-02 More than 90 days
TESTUSER   09-MAY-03 Current Month
PEASLAND   28-FEB-02 More than 90 days
OSE$HTTP$A 26-FEB-02 More than 90 days

OUTLN      26-FEB-02 More than 90 days

10 rows selected.
Hopefully, you can see how I subtracted the date field of my table from the current date (SYSDATE). I then computed the number of 30 day periods that have passed by dividing it by 30 and truncating to a integer. Depending on that result, I was able to determine if it was the current month (0), last month (1), etc. Finally, I used DECODE's default to provide for all instances over 90 days.

A very similar example of this is in Oracle 8i The Complete Reference by Loney & Koch on Oracle Press. They devote an entire chapter to DECODE. This is one of my most widely used books and I'd recommend it to anyone learning Oracle SQL.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.