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!