Oracle date difference with case
I have the followin开发者_开发知识库g:
select nvl (
(select 1
from tableA, tableB
where tableA.id(+) = 9999
and tableB.project_id(+) = tableA.project_id
and sysdate - tableA.start_date < 120), 0) as myFlag from dual;
I am trying to see if the project start date is less than 120 or not. The above query works but is there a better way of doing this? Like with a case statement?
select
case
when sysdate-tableA.start_date < 120 then 1
else 0
end myFlag
from tableA, tableB
where tableA.id(+) = 9999
and tableB.project_id(+) = tableA.project_id
Why are you left joining to table B? Couldn't you just do this?
SELECT CASE WHEN ID IS NULL THEN 0 ELSE 1 END
FROM TableA
WHERE sysdate - TableA.start_date < 120 and TableA.id = 9999;
精彩评论