Check user changed his password recently or not in specific period I am working on Oracle SQL. What I am trying to do is:


Query a record with ID
If the record does not exists use user input value
If the record exists, compare last_login_date column and user input value (I put to_date('20160401', 'yyyymmdd') then pick recent one)
Add not_change_day to result of 3, then compare the value with sysdate
If 4 is bigger then sysdate then returns true else false




select 
    case when (a.ncd + a.lld &gt; sysdate) then 'true' 
    else 'false' end as result
from
(
    select 
        nvl(not_change_day, 90) as ncd,
        case 
            when last_login_date is null then to_date('20160401','yyyymmdd')
            when last_login_date &gt; to_date('20160401','yyyymmdd') then last_login_date
        END as lld
    from 
        FT_PWD_CHANGE
    where 
        id='some_id'
) a


RESULT

result
------
false


However, I believe there are too many case when statements and sub-queries. Would you suggest a more efficient way?
Your query doesn't returns a result when there's no id matching 'some_id'. 

Well, there's a trick to force a row, use an aggregate function.

I think this is your logic rewritten to a single CASE (of course COALESCE is just a shortcut for a CASE :-)

SELECT COALESCE(MIN(CASE WHEN Greatest(COALESCE(last_login_date, to_date('20160401','yyyymmdd'))
                                      ,to_date('20160401','yyyymmdd')
                             + COALESCE(not_change_day, 90) &gt; sysdate 
                         THEN 'true'
                     END)
                    ,'false')
FROM FT_PWD_CHANGE
WHERE id='foo'


Btw, for a hard-coded date I would prefer DATE '2016-04-01' over to_date('20160401','yyyymmdd'), it's shorter and Standard SQL.