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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.