Re: DECODE or not to DECODE

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Mon, 04 Aug 2003 06:49:24 -0800
Message-ID: <F001.005C8D0B.20030804064924@fatcity.com>

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:jonathan@gennick.com

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 Oracle-article-request_at_gennick.com 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: jonathan_at_gennick.com

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: ListGuru_at_fatcity.com (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). Received on Mon Aug 04 2003 - 09:49:24 CDT