ajit_cus wrote
> Hi,
> This is my test case!!
> regards,
> Ajit
> create table tableX
> (
> datecolumn1 date
> )
> //
> Insert into tableX values(to_date('8/17/2000 12:00:00
> AM','MM/DD/YYYY HH:MI:SS AM'))
> //
> Insert into tableX values(to_date('1/30/2030 12:00:00
> AM','MM/DD/YYYY HH:MI:SS AM'))
> //
> Select TO_CHAR(datecolumn1,'MM/DD/YYYY HH:MI:SS AM') from TableX
> //
> returns:
> 08/17/2000 12:00:00 AM
> 01/30/2030 12:00:00 AM
> //
> Select TO_CHAR(datecolumn1,'MM/DD/YYYY HH:MI:SS AM') from
> TableX where datecolumn1=(Select max(datecolumn1) from TableX)
> //
> returns no rows (WHY???!!!) expect 01/30/2030 12:00:00 AM
> //
> Select TO_CHAR(max(datecolumn1),'MM/DD/YYYY HH:MI:SS AM') from TableX
> //
> returns
> 01/30/2030 12:00:00 AM
> //
> Select TO_CHAR(datecolumn1,'MM/DD/YYYY HH:MI:SS AM') from
> TableX where datecolumn1>(Select max(datecolumn1) from TableX)
> //
> returns
> 08/17/2000 12:00:00 AM
> 01/30/2030 12:00:00 AM
> (WHY???!!!) expect no rows
You are right, unfortunately there is a bug in the kernel which will always
result in unexpected results if
1. a date/time/timestamp-value is in the select-list of a subquery AND
2. another date_and_time_format than internal is used or sqlmode=oracle is used.
A workaround is
use
1. sqlmode internal AND
2. change
Select TO_CHAR(datecolumn1,'MM/DD/YYYY HH:MI:SS AM') to
Select CHAR(DATE(datecolumn1), USA) || ' ' || CHAR(TIME(datecolumn1), USA)
or
1. stay with sqlmode oracle
2. change the where-clause to
where TO_CHAR(datecolumn1,'MM/DD/YYYY HH:MI:SS AM') =
(Select TO_CHAR(max(datecolumn1),'MM/DD/YYYY HH:MI:SS AM') from TableX)
(in this case you change the datatype from one of the critical ones to CHAR)
but please keep in mind that the second version of workaround should only be
used together with =.
With > or < the result will be according to the character-values
( 31/01/1998... > 01/01/2003... ) which is usually not the wanted result.
And the second version of workaround will not be able to use a good search
strategy even if datecolumn1 is primary key column or secondary key column.
With the first version there is a chance to use a good one (depending on the
value, the statistics and so on.
It is not needed to use alias-names (as written by another guy). That makes no
difference for the semantic and behaviour of this select.
A LIKE-function for the second version of workaround is not better than a '='.
The TO_CHAR on both sides made the effect.
Of course we will fix this bug, not timeschedule so far.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
