From oracle-l-bounce@freelists.org Thu Jul 29 12:20:11 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6THJtU12165 for ; Thu, 29 Jul 2004 12:20:05 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6THJj612102 for ; Thu, 29 Jul 2004 12:19:55 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3CD8672D382; Thu, 29 Jul 2004 11:57:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13168-24; Thu, 29 Jul 2004 11:57:50 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3F93872DDD9; Thu, 29 Jul 2004 11:57:49 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Jul 2004 11:56:17 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 75AA672C601 for ; Thu, 29 Jul 2004 11:56:16 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11691-68 for ; Thu, 29 Jul 2004 11:56:16 -0500 (EST) Received: from troll.tpk.net (mail.tpk.net [216.107.198.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E7EEA72C10B for ; Thu, 29 Jul 2004 11:56:15 -0500 (EST) Received: from MWF600XL (host216-107-194-250.tpk.net [216.107.194.250]) by troll.tpk.net (Postfix) with SMTP id 05A19669092 for ; Thu, 29 Jul 2004 13:23:33 -0400 (EDT) From: "Mark W. Farnham" To: Subject: RE: Min of decode explaination Date: Thu, 29 Jul 2004 13:23:31 -0400 Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 6429 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org grrr. meant to_char -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Mark W. Farnham Sent: Thursday, July 29, 2004 1:20 PM To: oracle-l@freelists.org Subject: RE: Min of decode explaination or if speed matters, it might be slightly faster to use to_date YYYYMMHH24MISS, or something like that, which sorts in char correctly and avoids one conversion. Your mileage may vary. This is probably an unimportant difference, and probably the one you think reads more clearly is the better choice. mwf -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Mark W. Farnham Sent: Thursday, July 29, 2004 1:11 PM To: oracle-l@freelists.org Subject: RE: Min of decode explaination actually, 01.... is the lowest character value. you need to convert it back to a date before you apply the min function. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Raj Jamadagni Sent: Thursday, July 29, 2004 11:14 AM To: oracle-l@freelists.org Subject: Re: Min of decode explaination Try select min(plan_start_dt), min(decode(ord_stat,'OP','',to_char(plan_start_dt,'dd-mon-yy'))) from test_tbl; It's probably the implicit data conversion. ymmv Raj --- Lee Lee wrote: > grrrrr. > > This should be an easy question. > > I have the following table: > > SQL> desc test_tbl > Name Null? Type > ----------------------------- -------- ------------ > PART_NBR CHAR(25) > ORD_NBR CHAR(10) > PLAN_START_DT DATE > ORD_STAT CHAR(2) > > > > > With the following data: > > PART_NBR ORD_NBR PLAN_STAR OR > ------------------------- ---------- --------- -- > 1234-567 1002003004 07-DEC-04 FP > 1234-567 1002003004 07-DEC-04 FP > 1234-567 1002003004 06-DEC-04 FP > 1234-567 1002003004 06-DEC-04 FP > 1234-567 1002003004 03-DEC-04 FP > 1234-567 1002003004 03-DEC-04 FP > 1234-567 1002003004 02-DEC-04 FP > 1234-567 1002003004 02-DEC-04 FP > 1234-567 1002003004 01-DEC-04 FP > 1234-567 1002003004 30-NOV-04 FP > 1234-567 1002003004 29-NOV-04 FP > 1234-567 1002003004 23-NOV-04 FP > 1234-567 1002003004 22-NOV-04 FP > 1234-567 1002003004 19-NOV-04 FP > 1234-567 1002003004 18-NOV-04 FP > 1234-567 1002003004 17-NOV-04 FP > 1234-567 1002003004 16-NOV-04 FP > 1234-567 1002003004 15-NOV-04 FP > 1234-567 1002003004 11-NOV-04 FP > 1234-567 1002003004 10-NOV-04 FP > 1234-567 1002003004 10-NOV-04 FP > 1234-567 1002003004 10-NOV-04 FP > 1234-567 1002003004 09-NOV-04 FP > 1234-567 1002003004 08-NOV-04 FP > 1234-567 1002003004 05-NOV-04 FP > 1234-567 1002003004 05-NOV-04 FP > 1234-567 1002003004 03-NOV-04 FP > > Why does this query return different values for each > column, shouldn't they be the same. > > SQL> select min(plan_start_dt), > min(decode(ord_stat,'OP','',plan_start_dt)) > > from test_tbl; > > MIN(PLAN_ MIN(DECOD > --------- --------- > 03-NOV-04 01-DEC-04 > > 1 row selected. > > > Thanks, > Dummy > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - 50x more storage than other providers! > http://promotions.yahoo.com/new_mail > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ===== Best Regards Raj --------------------------------------------------------- select mandatory_disclaimer from company_requirements; __________________________________ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------