DECODE vs. CASE problem
Hi, sorry to post what may end up being a very dumb question here. I'm trying to take a huge, complicated query that runs a ton of DECODE statements and turn it into something readible. However, I'm running in to a strange issue.
To my mind, when I run the following query, columns A, B, and C should all return exactly the same values:
SELECT userid,
DECODE(MAX(last_committed_dt),NULL,MAX(launch_dt)+1,MAX(last_committed_dt))-(MIN(launch_dt)) A,
NVL(MAX(last_committed_dt), MAX(launch_dt)+1) - MIN(launch_dt) B,
CASE WHEN MAX(last_committed_dt) IS NOT NULL THEN MAX(last_committed_dt) ELSE MAX(launch_dt)+1 END - MIN(launch_dt) C
FROM mytable ttJ
left join mytable2 sa ON (ttJ.user_id = sa.user_id)
left join mytable3 lh ON (lh.attempt_id = sa.attempt_id)
GROUP BY user_id
However, the results of this query show that B and C equal, but neither ties to A:
USERID A B C
1134 +00 01:50:50.471849 +00 01:50:50.877240 +00 01:50:50.877240
6716 +37 00:36:02.605919 +37 00:36:02.994966 +37 00:36:02.994966
1118 +00 00:22:48.913212 +00 00:22:49.419523 +00 00:22:49.419523
3111 +53 23:43:03.858221 +53 23:43:04.122084 +53 23:43:04.122084
Could someone please help me understand the difference?
Thanks!
Scott