RE: Min of decode explaination
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Jul 2004 13:23:31 -0400
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 //www.freelists.org/archives/oracle-l/
> FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
