Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problems with GREATEST() => Bug?

777489Aug 12 2010 — edited Aug 13 2010
I'm working on a view and got a problem with the GREATEST()-function - I'm using 11.2.
Below I#ve past some code with comments which should explain the problem - but you're welcome to ask any more questions ;)
-- first executeing the query separated to check it return valus   2.
(SELECT nvl(max(acqtime),to_timestamp('01-01-1971','dd-mm-yyyy')) FROM rfm_meas_hr WHERE box_id IN (SELECT...WHERE id=4) );    
-- returns: 01.01.71 00:00:00,000000000
(SELECT nvl(max(acqtime),to_timestamp('01-01-1971','dd-mm-yyyy')) FROM rfm_meas_10m WHERE box_id IN (SELECT...WHERE id=4) );
-- returns: 11.08.10 16:00:00,000000000

-- therefore i assume if i use GREATEST() with this both timestamps ist would return the second one
 
SELECT GREATEST(
        to_timestamp('01-01-1971 00:00:00,000','dd-mm-yyyy hh24:mi:ss,ff'),
        to_timestamp('11-08-2010 16:00:00,000','dd-mm-yyyy hh24:mi:ss,ff')
) FROM dual;
-- do that job like expected
 
-- BUT
SELECT GREATEST(
        (SELECT nvl(max(acqtime),to_timestamp('01-01-1971','dd-mm-yyyy')) FROM rfm_meas_hr WHERE box_id IN (SELECT...WHERE id=4) ),
        (SELECT nvl(max(acqtime),to_timestamp('01-01-1971','dd-mm-yyyy')) FROM rfm_meas_10m WHERE box_id IN (SELECT...WHERE id=4) )
) FROM dual;
-- returns the 1971 timestamp wich is the wrong result!
 
-- any ideas?
alternativly you can view the code on pastebin: http://pastebin.com/uGVDUMuU

I want to know if this behaviour of GREATEST() is on purpose or if I did some mistake or anything else!?

Thanks!
This post has been answered by Boneist on Aug 12 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 10 2010
Added on Aug 12 2010
27 comments
2,665 views