Q
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;

USERNAME   CREATED   TIME_FRAME
---------- --------- -----------------
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
$UTILITY$

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

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
DMIN

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close