In the first example you are comparing the actual date values, in the second
example you are comparing the strings which represent the date values.
For example when comparing the strings the date represented by the string
11-JAN-2003 10:00:00
will be considered greater that the date represented by the string
11-APR-2003 10:00:00
because in a string comparison the first different character is at position
4 and in the first value "J" is greater than "A".
If you have to compare date string values, you would need to format the date
such that the most significant parts of the date come before the least
significant parts, for example 'YYYYMMDDHI24MISS'.
HTH
-----Original Message-----
From: wan loo [mailto:[Email Address Removed] Wednesday, April 30, 2003 12:11 PM
To: LazyDBA.com Discussion
Subject: Queries returning different result sets
Hi DBAs,
There are two queries here that our team expected the same result.
But when we run them, the results are different. The first result is the
correct one.
1 SELECT
2 count(*)
3 FROM valu_daily_unit_values_jt
4 WHERE jt_datetime >=
5 (select ended_datetime from valu_flow_status
6 where institution_id like 'NYSE' and as_of_date = '25-APR-2003')
7* and jt_operation != 'DEL'
COUNT(*)
----------
283441
SQL> r
1 SELECT
2 count(*)
3 FROM valu_daily_unit_values_jt
4 WHERE to_char(jt_datetime,'DD-MON-YYYY HH24:MI:SS') >=
5 (select to_char(ended_datetime,'DD-MON-YYYY HH24:MI:SS') from
valu_flow_status
6 where institution_id like 'NYSE' and as_of_date = '25-APR-2003')
7* and jt_operation != 'DEL'
COUNT(*)
----------
1095276
What we think are the date format and to_char function. But what is the
explanation?
Pls advise. Thanks in advance.
Jeremy
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page