# Check user changed his password recently or not in specific period

I am working on Oracle SQL. What I am trying to do is:

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

select
case when (a.ncd + a.lld > 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')
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?

• Please tell us what you are really trying to do, and also make that the title of your question. (It should probably be something about recent password changes.) – 200_success Oct 26 '16 at 3:21
• @200_success Is it ok for now? I made title more specific but feels like it is too specific topic. – Juneyoung Oh Oct 26 '16 at 4:20
• I've removed the remark about "not working", as that would make your question off-topic for Code Review. – 200_success Oct 26 '16 at 4:54

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) > 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.