RE: Min of decode explaination

grrr. meant to_char

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mark W. Farnham
Sent: Thursday, July 29, 2004 1:20 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mark W. Farnham
Sent: Thursday, July 29, 2004 1:11 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Raj Jamadagni
Sent: Thursday, July 29, 2004 11:14 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: