1
\$\begingroup\$

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')
            when last_login_date > 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?

\$\endgroup\$
  • \$\begingroup\$ 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.) \$\endgroup\$ – 200_success Oct 26 '16 at 3:21
  • \$\begingroup\$ @200_success Is it ok for now? I made title more specific but feels like it is too specific topic. \$\endgroup\$ – Juneyoung Oh Oct 26 '16 at 4:20
  • \$\begingroup\$ I've removed the remark about "not working", as that would make your question off-topic for Code Review. \$\endgroup\$ – 200_success Oct 26 '16 at 4:54
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.