how can I use subquery in CASE THEN?

Question

I tried to use subquery in CASE THEN like below.(I wrote down the code in ORACLE)

WITH SAMPLE AS (

SELECT 1 AS VALUE_, 
       '20191230' AS DATE_
FROM DUAL

UNION

SELECT 2 AS VALUE_, 
       '20201230' AS DATE_
FROM DUAL)

SELECT VALUE_,
       DATE_,
       CASE
           WHEN TO_CHAR(ADD_MONTHS(DATE, -12), 'YYYYMMDD') IN (SELECT DATE_ FROM SAMPLE)
                THEN (SELECT VALUE_ FROM SAMPLE WHERE DATE_ = TO_CHAR(ADD_MONTHS(DATE, -12), 
                     'YYYYMMDD'))
           ELSE NULL
       END AS ONE_YEAR_AGO_VALUE_
FROM SAMPLE;

I expected to get a new column(ONE_YEAR_AGO_VALUE_) that has NULL in first row and 1 in second row.

However, result was different then I expected.

The result had a 'ONE_YEAR_AGO_VALUE_' that had NULL in first row and NULL in second row.

I want to make second row have 1.

If you let me know the wrong thing in my code, I really appreciate that.

Thank you.

Answer 1

When I fix your query to have correct use of dates, then it produces one row with NULL and one with 1:

WITH SAMPLE AS (
      SELECT 1 AS VALUE_, DATE '2019-12-30' AS DATE_
      FROM DUAL
      UNION ALL
      SELECT 2 AS VALUE_, DATE '2020-12-30' AS DATE_
      FROM DUAL
     ) 
SELECT S.VALUE_, S.DATE_,
       (CASE WHEN ADD_MONTHS(S.DATE_, -12) IN (SELECT S2.DATE_ FROM SAMPLE S2)
             THEN (SELECT S2.VALUE_ FROM SAMPLE S2 WHERE S2.DATE_ = ADD_MONTHS(S.DATE_, -12))
       END) AS ONE_YEAR_AGO_VALUE_
FROM SAMPLE S;

Your code is also overly complicated. You don't need a CASE expression, which you can use directly for the value. Here is a db<>fiddle.