RE: Min of decode explaination

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 29 Jul 2004 13:20:20 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKAEPAFBAA.mwf@rsiz.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham Sent: Thursday, July 29, 2004 1:11 PM
To: oracle-l_at_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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Raj Jamadagni Sent: Thursday, July 29, 2004 11:14 AM
To: oracle-l_at_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 <dumbdba_at_yahoo.com> 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_at_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_at_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_at_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_at_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
-----------------------------------------------------------------

Received on Thu Jul 29 2004 - 12:17:04 CDT