One possibility that comes to mind is to write a really, really long DECODE:
DECODE((SYSDATE - COL_DATE), 1, '30_days', 2, '30_days', 3, '30_days', ... NULL) Days30 But there must be a better possibility. Perhaps use the SIGN function: DECODE ( SIGN((SYSDATE - COL_DATE)-30), -1, '30_DAYS', 0, '30_DAYS', NULL) If the difference is 1-29, subtracting 30 will result in a negative value, and SIGN will return -1. If the difference is exactly 30, the subtraction will result in 0, and SIGN will return 0. The only other possible return is 1, for positive numbers, and I let the default (NULL) handle that case. My guess is that you could do something similar for your second DECODE statement, though the math would get a bit more complex. I'd have to think a bit longer to work up a solution, but I'm optimistic that there is one. Hope this helps. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. Monday, August 4, 2003, 10:24:22 AM, you wrote: DE> Hello Listers, DE> I need some help please, DE> I am trying to create a DECODE statement, on a date column and looks DE> something like this, but does not work. DE> SELECT COL1, DE> COL2, DE> DECODE((SYSDATE - COL_DATE), <= 30, '30_days', NULL) Days30, DE> DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days', DE> NULL) Days60, DE> ....... DE> ....... DE> FROM ACCOUNT_TBL DE> WHERE COL = '0' DE> Obviously, I have worked out that this can not be done. The problem is also DE> that the server is 7.3.4 and I need to use the code in a view. DE> Does anyone have any solutions/work arounds for this? DE> Any help will be appraciated. DE> TIA DE> Denham Eva DE> Oracle DBA DE> _____________________________________________________________________________________ DE> This e-mail message has been scanned for Viruses and Content and cleared DE> by MailMarshal DE> For more information please visit www.marshalsoftware.com DE> _____________________________________________________________________________________ DE> ##################################################################################### DE> Note: DE> This message is for the named person's use only. It may contain confidential, DE> proprietary or legally privileged information. No confidentiality or privilege DE> is waived or lost by any mistransmission. If you receive this message in error, DE> please immediately delete it and all copies of it from your system, destroy any DE> hard copies of it and notify the sender. You must not, directly or indirectly, DE> use, disclose, distribute, print, or copy any part of this message if you are not DE> the intended recipient. TFMC and any of its subsidiaries each reserve DE> the right to monitor all e-mail communications through its networks. DE> Any views expressed in this message are those of the individual sender, except where DE> the message states otherwise and the sender is authorized to state them to be the DE> views of any such entity. DE> Thank You. DE> -- DE> Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
