Oracle SQL case condition involving one column but changing another-CodePudding

I'm trying to change the sortest_tesc_code of any tuple whose sortest_test_date is greater than 5 years to ZZZ

select sortest_pidm, sortest_tesc_code 
     case sortest_tesc_code 
     when extract(year from sysdate)-extract(year from sortest_test_date)>=5 then 'ZZZ' 
          else sortest_tesc_code end as sortest_tesc_code
from tempTbl2

assuming tempTbl2 (derived) is this:

Sample data:

MOD7    1   10-JAN-11
MOD9    1   10-JAN-11
MPA3    1   19-JUL-11
C012    3   01-JUL-18
C030    3   01-JUL-17
MPA2    1   12-JUL-18

Change to:

ZZZ     1   10-JAN-11
ZZZ     1   10-JAN-11
ZZZ     1   19-JUL-11
C012    3   01-JUL-18
C030    3   01-JUL-17
MPA2    1   12-JUL-18

CodePudding user response：

You are mixing the simple and searched case expression syntax; and your calculation of 'five years' is a bit odd - I would base it on months between the two dates, not just look at the year numbers:

with tempTbl2(sortest_tesc_code, testscore, sortest_test_date) as (
  select 'MOD7', 1, date '2011-01-10' from dual union all
  select 'MOD9', 1, date '2011-01-10' from dual union all
  select 'MPA3', 1, date '2011-07-19' from dual union all
  select 'C012', 3, date '2018-07-01' from dual union all
  select 'C030', 3, date '2017-07-01' from dual union all
  select 'MPA2', 1, date '2018-07-12' from dual
)
select
  case
    when add_months(sysdate, -60) > sortest_test_date then 'ZZZ' 
    else sortest_tesc_code
  end as sortest_tesc_code,
  testscore,
  sortest_test_date
from tempTbl2

SORTEST_TESC_CODE	TESTSCORE	SORTEST_TEST_DATE
ZZZ	1	10-JAN-11
ZZZ	1	10-JAN-11
ZZZ	1	19-JUL-11
C012	3	01-JUL-18
C030	3	01-JUL-17
MPA2	1	12-JUL-18

db<>fiddle

Using extract(year from sysdate)-extract(year from sortest_test_date)>=, any date before 2018-01-01 will match; treating a date that is, as of today, only 4 years and 42 days ago as more than 5 years ago seems wrong to me. Perhaps you have a legitimate reason to do that, of course, in which case just change the condition back to what you had.

CodePudding user response：

You merely got a little confused with the CASE syntax.

select
   case when extract(year from sysdate) - extract(year from sortest_test_date) >= 5 
     then 'ZZZ' 
     else sortest_tesc_code
  end as new_sortest_tesc_code,
  testscore,
  sortest_test_date
from tempTbl2
order by sortest_test_date, sortest_tesc_code;