RE: decode
Use greatest ... decode(greatest(sn,7),sn, substr(sn,-7),sn ) Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Imran Ashraf Sent: Wednesday, September 03, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Hi, I have this query... select decode(length(sn),8),substr(sn,-7),sn )from . However i want to change it so that if the length of sn is = 8 then set sn to last 7 characters. How can i implement this? Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette 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).
RE: decode
decode(sign(8 - length(sn)), -1, substr(sn,-7), sn ) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Imran Ashraf Sent: Wednesday, September 03, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Hi, I have this query... select decode(length(sn),8),substr(sn,-7),sn )from . However i want to change it so that if the length of sn is = 8 then set sn to last 7 characters. How can i implement this? Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman 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).
Re: decode
Imran Ashraf wrote: Hi, I have this query... select decode(length(sn),8),substr(sn,-7),sn )from . However i want to change it so that if the length of sn is = 8 then set sn to last 7 characters. How can i implement this? Regards -- decode(sign(length(sn) - 8, 1, ..., 0, ..., sn) -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
RE: DECODE or not to DECODE
Thanks To all for the support! I managed to use a combination of thesuggestions which worked great! Denham -Original Message- Sent: Monday, August 04, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Hello Listers, I need some help please, I am trying to create a DECODE statement, on a date column and looks something like this, but does not work. SELECT COL1, COL2, DECODE((SYSDATE - COL_DATE), = 30, '30_days', NULL) Days30, DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days', NULL) Days60, .. .. FROM ACCOUNT_TBL WHERE COL = '0' Obviously, I have worked out that this can not be done. The problem is also that the server is 7.3.4 and I need to use the code in a view. Does anyone have any solutions/work arounds for this? Any help will be appraciated. TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ # Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denham Eva 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). _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ # Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denham Eva 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).
RE: DECODE or not to DECODE
One trick is to use the sign() function... decode(sign(30 - sysdate + col_date), 1, '30_days', NULL), Regards David Lord -Original Message- From: Denham Eva [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 15:24 To: Multiple recipients of list ORACLE-L Subject: DECODE or not to DECODE Hello Listers, I need some help please, I am trying to create a DECODE statement, on a date column and looks something like this, but does not work. SELECT COL1, COL2, DECODE((SYSDATE - COL_DATE), = 30, '30_days', NULL) Days30, DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days', NULL) Days60, ... ... FROM ACCOUNT_TBL WHERE COL = '0' Obviously, I have worked out that this can not be done. The problem is also that the server is 7.3.4 and I need to use the code in a view. Does anyone have any solutions/work arounds for this? Any help will be appraciated. TIA Denham Eva Oracle DBA __ ___ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com __ ___ ## ### Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denham Eva 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). *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** This e-mail and its attachments are intended for the author's addressee only and may be confidential. If they have come to you in error you must take no action based on them, nor must you copy or show them to anyone; please reply to this e-mail and highlight the error. Please note that this e-mail has been created in the knowledge that Internet e-mail is not a 100% secure communications medium. We advise that you understand and observe this lack of security when e-mailing us. Steps have been taken to ensure this e-mail and attachments are free from any virus, but advise the recipient to ensure they are actually virus free. The views, opinions and judgments expressed in this message are solely those of the author. The message contents have not been reviewed or approved by Iron Mountain. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lord David 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).
Re: DECODE or not to DECODE
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).
RE: DECODE or not to DECODE
Eva - I dredged this out of some ancient code that I wrote against a 7.2 database. The inner trunc functions were there to get rid of the time - I don't remember why the outer trunc function was there at this point (it was five years ago). This doesn't get you completely there, but will hopefully get you started. Decode (sign(30-(trunc(trunc(SysDate)-trunc(col_date, 1,'30 days',null) Carol Bristow DPRA Inc. 1300 N 17th St Suite 950 Rosslyn, VA 22209 Work: 703-841-8025 Fax: 703-524-9415 -Original Message- Sent: Monday, August 04, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Hello Listers, I need some help please, I am trying to create a DECODE statement, on a date column and looks something like this, but does not work. SELECT COL1, COL2, DECODE((SYSDATE - COL_DATE), = 30, '30_days', NULL) Days30, DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days', NULL) Days60, ... ... FROM ACCOUNT_TBL WHERE COL = '0' Obviously, I have worked out that this can not be done. The problem is also that the server is 7.3.4 and I need to use the code in a view. Does anyone have any solutions/work arounds for this? Any help will be appraciated. TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ # Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denham Eva 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carol Bristow 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).
RE: DECODE or not to DECODE
Hmm. I think this might be better decode(sign(sysdate - col_date - 31), -1, '30_days', NULL) Days30, decode(sign(sysdate - col_date - 31), -1, NULL, decode(sign(sysdate - col_date - 61), -1, '60_days', NULL)) Days60 -Original Message- From: Lord David [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 15:39 To: Multiple recipients of list ORACLE-L Subject: RE: DECODE or not to DECODE One trick is to use the sign() function... decode(sign(30 - sysdate + col_date), 1, '30_days', NULL), Regards David Lord -Original Message- From: Denham Eva [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 15:24 To: Multiple recipients of list ORACLE-L Subject: DECODE or not to DECODE Hello Listers, I need some help please, I am trying to create a DECODE statement, on a date column and looks something like this, but does not work. SELECT COL1, COL2, DECODE((SYSDATE - COL_DATE), = 30, '30_days', NULL) Days30, DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days', NULL) Days60, ... ... FROM ACCOUNT_TBL WHERE COL = '0' Obviously, I have worked out that this can not be done. The problem is also that the server is 7.3.4 and I need to use the code in a view. Does anyone have any solutions/work arounds for this? Any help will be appraciated. TIA Denham Eva Oracle DBA __ ___ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com __ ___ ## ### Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denham Eva 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). *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** This e-mail and its attachments are intended for the author's addressee only and may be confidential. If they have come to you in error you must take no action based on them, nor must you copy or show them to anyone; please reply to this e-mail and highlight the error. Please note that this e-mail has been created in the knowledge that Internet e-mail is not a 100% secure communications medium. We advise that you understand and observe this lack of security when e-mailing us. Steps have been taken to ensure this e-mail and attachments are free from any virus, but advise the recipient to ensure they are actually virus free. The views, opinions and judgments expressed in this message are solely those of the author. The message contents have not been reviewed or approved by Iron Mountain. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lord David 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
Re: DECODE AND SELECT
- Original Message - I am in urgent need of backporting oracle9i pl/sql to oracle8i.I have encountered some case satements like this CASE when x0 then (select y from deptno) else (select Z from emp) end i need to convert them to decode statements.Can anybody tell me how to write a select statement within decode. You already got some suggestions. But if you're backporting from 9i to 8i, don't bother: 8i supports CASE happily. At least in SQL... Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: DECODE AND SELECT
Oracle 8i (8.1.6+) PL/SQL can use execute immediate for SQL with searched CASE expressions. Have Fun :) Nuno Souto wrote: - Original Message - I am in urgent need of backporting oracle9i pl/sql to oracle8i.I have encountered some case satements like this CASE when x0 then (select y from deptno) else (select Z from emp) end i need to convert them to decode statements.Can anybody tell me how to write a select statement within decode. You already got some suggestions. But if you're backporting from 9i to 8i, don't bother: 8i supports CASE happily. At least in SQL... Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chip 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).
Re: DECODE AND SELECT
You are lucky! My sinuses are getting better. Decode is a function which is to be called within a SQL statement, not the other way round. What do you translate a CASE statement into? That's elementary, my dear Hrishy: a sequence of if ... then ... else ... end if commands. On 2003.07.12 00:39, hrishy wrote: Hi I am in urgent need of backporting oracle9i pl/sql to oracle8i.I have encountered some case satements like this CASE when x0 then (select y from deptno) else (select Z from emp) end i need to convert them to decode statements.Can anybody tell me how to write a select statement within decode. regards Hrishy Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?hrishy?= 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
RE: DECODE AND SELECT
One thing you could consider is to make functions out of each of the sql statements in the case. Then, in your decode statement you could call the correct function based on the value of X. Either that or put the entire logic into a function that has all the case statement broken down into individual IF tests . -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/11/03 11:39 PM Hi I am in urgent need of backporting oracle9i pl/sql to oracle8i.I have encountered some case satements like this CASE when x0 then (select y from deptno) else (select Z from emp) end i need to convert them to decode statements.Can anybody tell me how to write a select statement within decode. regards Hrishy Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?hrishy?= 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange 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).
RE: Decode
Jared, didn't you mean: select chr(84)||chr(104)||chr(97) ||chr(110)||chr(107)||chr(32) ||chr(71)||chr(111)||chr(100) ||chr(33) from dual Just being picky... :D Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 11, 2002 7:33 PM To: Multiple recipients of list ORACLE-L Subject: Re: Decode No, I didn't know that. Interesting: select decode ( (select to_char(sysdate, 'DAY') from dual), 'MONDAY','Groan...', 'TUESDAY','Another day, another dollar', 'WEDNESDAY','Hump Day', 'THURSDAY','Let me out of here!', 'FRIDAY',( select chr(84)||chr(104)||chr(97) ||chr(110)||chr(107)||chr(32) ||chr(103)||chr(111)||chr(100) ||chr(33) from dual ), 'SATURDAY','Sleeping in', 'SUNDAY','Gone fishing' ) from dual / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: Decode
Good example! ;=) Thanks, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 11, 2002 3:38 PM select decode(new_job_loc,'Vermont','TAKE IT','LEAVE IT') from dual; Waleed -Original Message- Sent: Wednesday, September 11, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
Beware...nasty sql below
select decode((select deptno from dept where upper(loc) = upper('vloc')),
-- expression
(select deptno from dept where loc = 'NEW YORK'),
-- value1
(select count(*) from emp where deptno = 10),
-- return1
(select deptno from dept where loc = 'DALLAS'),
-- value2
(select count(*) from emp where deptno = 20),
-- return2
(select deptno from dept where loc = 'CHICAGO'),
-- value3
(select count(*) from emp where deptno = 30),
-- return3
(select count(*) from emp where deptno not in (10,20,30))
-- default_return
) emp_count
from dual
SQL /
Enter value for vloc: DALLAS
EMP_COUNT
--
5
SQL /
Enter value for vloc: Chicago
EMP_COUNT
--
6
SQL /
Enter value for vloc: DENVER
EMP_COUNT
--
0
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
RE: Decode
here's a decode that should *NEVER* be put into production! :)
-Original Message-
Sent: Thursday, September 12, 2002 12:18 PM
To: Multiple recipients of list ORACLE-L
Beware...nasty sql below
select decode((select deptno from dept where upper(loc) = upper('vloc')),
-- expression
(select deptno from dept where loc = 'NEW YORK'),
-- value1
(select count(*) from emp where deptno = 10),
-- return1
(select deptno from dept where loc = 'DALLAS'),
-- value2
(select count(*) from emp where deptno = 20),
-- return2
(select deptno from dept where loc = 'CHICAGO'),
-- value3
(select count(*) from emp where deptno = 30),
-- return3
(select count(*) from emp where deptno not in (10,20,30))
-- default_return
) emp_count
from dual
SQL /
Enter value for vloc: DALLAS
EMP_COUNT
--
5
SQL /
Enter value for vloc: Chicago
EMP_COUNT
--
6
SQL /
Enter value for vloc: DENVER
EMP_COUNT
--
0
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
RE: Decode
Title: RE: Decode Wait till you see some of the SQL that we run ... it is dynamic sql used within SQR (a reporting tool). Compared to that, this is production quality. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 12:38 PM To: Multiple recipients of list ORACLE-L Subject: RE: Decode here's a decode that should *NEVER* be put into production! :) *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Decode
Title: RE: Decode Raj, I know SQR. Bought it when it was being peddled by Sql Solutions. Great tool! Somehow, it never got marketed correctly. I had heard that at one point, Oracle "almost" bought it, and was going to position it as one of its products. But that boat has sailed. Tom Mercadante Oracle Certified Professional -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 12, 2002 2:08 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Decode Wait till you see some of the SQL that we run ... it is dynamic sql used within SQR (a reporting tool). Compared to that, this is production quality. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 12:38 PM To: Multiple recipients of list ORACLE-L Subject: RE: Decode here's a decode that should *NEVER* be put into production! :)
RE: Decode
Well, if we're going to be picky :) select chr(84)||chr(104)||chr(97) ||chr(110)||chr(107)||chr(32) ||chr(71)||chr(111)||chr(100) ||chr(101)||chr(115)||chr(115) ||chr(33) from dual Jared, didn't you mean: select chr(84)||chr(104)||chr(97) ||chr(110)||chr(107)||chr(32) ||chr(71)||chr(111)||chr(100) ||chr(33) from dual Just being picky... :D Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA gt; -Original Message- gt; From: Jared Still [mailto:[EMAIL PROTECTED]] gt; Sent: Wednesday, September 11, 2002 7:33 PM gt; To: Multiple recipients of list ORACLE-L gt; Subject: Re: Decode gt; gt; gt; gt; No, I didn't know that. gt; gt; Interesting: gt; gt; select decode ( gt;(select to_char(sysdate, 'DAY') from dual), gt;'MONDAY','Groan...', gt;'TUESDAY','Another day, another dollar', gt;'WEDNESDAY','Hump Day', gt;'THURSDAY','Let me out of here!', gt;'FRIDAY',( gt; select gt; chr(84)||chr(104)||chr(97) gt; ||chr(110)||chr(107)||chr(32) gt; ||chr(103)||chr(111)||chr(100) gt; ||chr(33) gt; from dual gt; ), gt;'SATURDAY','Sleeping in', gt;'SUNDAY','Gone fishing' gt; ) gt; from dual gt; / -- Please see the official ORACLE-L FAQ: a href=http://mail.thirdparadigm.com//jump/http://www.orafaq.com;http://www.orafaq.com/a -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). Christopher Beckley ThirdParadigm LLC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: RE: Decode
Dick, But is was a fun tool to program in! It was so string in that it (at the time) was the only tool that could interface with OS files, Oracle, and produce output at the same time. We bought it instead of RPT and RPF. I almost had a heart-attack when Oracle classes showed us those POC and said this is our batch tool. I started searching immediately as we wanted to throw away Cobol. SQR was the only choice. (This is back about 10 years ago). Have you tried the Reverse Hebrew Wrap printing option? :) When things got slow, I'd bring that baby up for a few laughs. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 12, 2002 3:58 PM To: Multiple recipients of list ORACLE-L Tom, I regret to say that SQR is still running around. SOD awful piece of crap that should have dies along with RPT and RPF. PeopleSoft makes a lot of use of it it is a REAL performance boat anchor. Dick Goulet Reply Separator Author: Mercadante; Thomas F [EMAIL PROTECTED] Date: 9/12/2002 11:03 AM Raj, I know SQR. Bought it when it was being peddled by Sql Solutions. Great tool! Somehow, it never got marketed correctly. I had heard that at one point, Oracle almost bought it, and was going to position it as one of its products. But that boat has sailed. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 12, 2002 2:08 PM To: Multiple recipients of list ORACLE-L Wait till you see some of the SQL that we run ... it is dynamic sql used within SQR (a reporting tool). Compared to that, this is production quality. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 12, 2002 12:38 PM To: Multiple recipients of list ORACLE-L here's a decode that should *NEVER* be put into production! :) !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 TITLERE: Decode/TITLE META content=MSHTML 5.50.4912.300 name=GENERATOR/HEAD BODY DIVSPAN class=140375317-12092002FONT face=Arial color=#ff size=2Raj,/FONT/SPAN/DIV DIVSPAN class=140375317-12092002FONT face=Arial color=#ff size=2/FONT/SPANnbsp;/DIV DIVSPAN class=140375317-12092002FONT face=Arial color=#ff size=2I know SQR.nbsp; Bought it when it was being peddled by Sql Solutions.nbsp; Great tool!nbsp; Somehow, it never got marketed correctly.nbsp; I had heard that at one point, Oracle almost bought it, and was going to position it as one of its products.nbsp; But that boat has sailed./FONT/SPAN/DIV DIVFONT face=Arial size=2/FONTnbsp;/DIV DIVFONT face=Arial size=2Tom Mercadante/FONT BRFONT face=Arial size=2Oracle Certified Professional/FONT /DIV BLOCKQUOTE dir=ltr style=MARGIN-RIGHT: 0px DIV class=OutlookMessageHeader dir=ltr align=leftFONT face=Tahoma size=2-Original Message-BRBFrom:/B Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]BRBSent:/B Thursday, September 12, 2002 2:08 PMBRBTo:/B Multiple recipients of list ORACLE-LBRBSubject:/B RE: DecodeBRBR/FONT/DIV PFONT size=2Wait till you see some of the SQL that we run ... it is dynamic sql used within SQR (a reporting tool). Compared to that, this is production quality./FONT/P PFONT size=2Raj/FONT BRFONT size=2__/FONT BRFONT size=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; MIS, ESPN Inc./FONT BRFONT size=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT size=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONTBRFONT size=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /PBR PFONT size=2-Original Message-/FONT BRFONT size=2From: Mercadante, Thomas F [A href=mailto:[EMAIL PROTECTED];mailto:[EMAIL PROTECTED]/A] /F ONT BRFONT size=2Sent: Thursday, September 12, 2002 12:38 PM/FONT BRFONT size=2To: Multiple recipients of list ORACLE-L/FONT BRFONT size=2Subject: RE: Decode/FONT /PBR PFONT size=2here's a decode that should *NEVER* be put into production!nbsp; :)/FONT /P/BLOCKQUOTE/BODY/HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Decode
Ruth, You can pull manuals off the web at this URL: http://download-east.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm This is for 9i. If you have 8i, they have that too. Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 11, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject:Decode Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
DECODE(WHAT ARE YOU DECODING,IF YOU FIND THIS VALUE 1,REPLACE IT WITH THIS VALUE 1,IF YOU FIND THIS VALUE 2,REPLACE IT WITH THIS VALUE 2,FOR ALL OTHER CASES REPLACE IT WITH THIS VALUE); -Original Message- Sent: Wednesday, September 11, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: Decode
Ruth, The DECODE is the equivelent of IF,THEN,ELSE. select Decode(column, value IF, value THEN, value ELSE) from tablename; Ron ROR [EMAIL PROTECTED] 09/11/02 02:59PM Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
Here you go, Ruth, select decode(field,'data value1','output1','data value2','output2',...'output if none others') MEANINGFUL_ALIAS from yadayada Rodd On Wed, 2002-09-11 at 14:28, Carle, William T (Bill), ALCAS wrote: Ruth, You can pull manuals off the web at this URL: http://download-east.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm This is for 9i. If you have 8i, they have that too. Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 11, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Decode Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
decode(test_value,if_A,then_B,if_C,then_D,...,otherwise_E) decode(city,'San Francisco','CA','Dallas','TX','Unknown') If the city is 'San Francisco', return 'CA'. If the city is 'Dallas', return 'TX' If neither, return 'Unknown' -Original Message- Sent: Wednesday, September 11, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
Ruth - Google! Google! Decode Oracle's handy decode function works as follows: decode(expr, search, expr[, search, expr...] [, default]) To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 11, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
DECODE(value,if1,then1,if2,then2,if3,then3,...else) Have a great day Ruth!! -Original Message- Sent: Wednesday, September 11, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
DECODE(expression, value1, return1, value2, return2, default_return) BTW, did you know that expression, value, return and default_return can all be scalar subqueries? Dan -Original Message- Sent: Wednesday, September 11, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: Decode
decode (column_name, value, replacement, value, replacement, default) where value is the column value replacement is value you want to replace the column value with default is the value to use if you get a column value not in your list decode (operating_system, 'PC','Windows','SUN','UNIX','LINUX') so if the operating_system column has the value PC, you want to have Windows as the value you actually get,if it has SUN you get UNIX, if it has anything else you will see LINUX --- Ruth Gramolini [EMAIL PROTECTED] wrote: Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
select decode(new_job_loc,'Vermont','TAKE IT','LEAVE IT') from dual; Waleed -Original Message- Sent: Wednesday, September 11, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
Title: RE: Decode an example straight from 9i SQL Reference should do SELECT product_id, DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non-domestic') quantity_on_hand FROM inventories; -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 11, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Subject: Decode Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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).
Re: Decode
No, I didn't know that. Interesting: select decode ( (select to_char(sysdate, 'DAY') from dual), 'MONDAY','Groan...', 'TUESDAY','Another day, another dollar', 'WEDNESDAY','Hump Day', 'THURSDAY','Let me out of here!', 'FRIDAY',( select chr(84)||chr(104)||chr(97) ||chr(110)||chr(107)||chr(32) ||chr(103)||chr(111)||chr(100) ||chr(33) from dual ), 'SATURDAY','Sleeping in', 'SUNDAY','Gone fishing' ) from dual / Jared On Wednesday 11 September 2002 12:33, Fink, Dan wrote: DECODE(expression, value1, return1, value2, return2, default_return) BTW, did you know that expression, value, return and default_return can all be scalar subqueries? Dan -Original Message- Sent: Wednesday, September 11, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: DECODE HELP SOLVED
Thanks Lyuda and Ron for your help! --- Lyuda Hoska [EMAIL PROTECTED] wrote: Something like this would work. SELECT GREATEST(FIRST_DATE, NVL(SEC_DATE,THIRD_DATE),NVL(THIRD_DATE,SEC_DATE)) FROM your_table; Just an idea, you can explore it further.. -Original Message- Sent: Monday, May 20, 2002 3:48 PM To: Multiple recipients of list ORACLE-L Hello all, This has probably been discussed here before and I'm sorry if I'm repeating this. But I'd very much appreciate if someone could show me how to use DECODE to select a MAX date where records are like this: PRIM. KEY FIRST_DATE SEC_DATE THIRD_DATE JO001599O 12/14/2000 03/23/2001 05/21/2001 JO001620L12/27/2000 05/16/2001 The first_date, sec_date and third_date may or may not exist; either one of the dates will be there. I need to come up with another column LATEST_DATE which will show the latest date of the 3. Thanks very much in advance! __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lyuda Hoska INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: DECODE HELP
Why use decode. Look up the greatest sql function. select prim_key, greatest(first_date,sec_date,third_date) from your_table. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] The problem with some people is that when they aren't drunk, they're sober. --William Butler Yeats. [EMAIL PROTECTED] mTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: DECODE HELP 05/20/02 01:48 PM Please respond to ORACLE-L Hello all, This has probably been discussed here before and I'm sorry if I'm repeating this. But I'd very much appreciate if someone could show me how to use DECODE to select a MAX date where records are like this: PRIM. KEY FIRST_DATE SEC_DATE THIRD_DATE JO001599O 12/14/2000 03/23/2001 05/21/2001 JO001620L12/27/2000 05/16/2001 The first_date, sec_date and third_date may or may not exist; either one of the dates will be there. I need to come up with another column LATEST_DATE which will show the latest date of the 3. Thanks very much in advance! __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode in PL/Sql
Feuerstein recommends replacing with IF statements. Page 517 Oracle PL/SLQ Programming. Simon Fox Room 205 Rail House Gresty Road CREWE Cheshire CW2 6EA England 01270 533997 -Original Message- Sent: 17 May 2001 10:26 To: Multiple recipients of list ORACLE-L Hi Gurus ! Is there any function that can be used in PL/SQL and just works the way as DECODE does in Sql? Thanks in Advance Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of Sema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the Sema UK Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: FOX, Simon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: Decode in PL/Sql
Gyula, I understand, that you want to apply DECODE function to some local variables in your pl/sql block (and not to some columns in some table). The trick is: you can always run DECODE() against 'dual' table: select DECODE(base_expr, compare1, value1, compare2, value2,... default ) into local_variable_out from dual; In this case 'base_expr' does not have to reference any column in any table. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 5:26 AM Hi Gurus ! Is there any function that can be used in PL/SQL and just works the way as DECODE does in Sql? Thanks in Advance Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
