title: how can I use subquery in CASE THEN?
body: <p>I tried to use subquery in CASE THEN like below.(I wrote down the code in ORACLE)</p>
  <pre><code>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;
  </code></pre>
  <p>I expected to get a new column(ONE_YEAR_AGO_VALUE_) that has NULL in first row and 1 in second row.</p>
  <p>However, result was different then I expected.</p>
  <p>The result had a 'ONE_YEAR_AGO_VALUE_' that had NULL in first row and NULL in second row.</p>
  <p>I want to make second row have 1.</p>
  <p>If you let me know the wrong thing in my code, I really appreciate that.</p>
  <p>Thank you.</p>