title: The difference between CASE and UPIVOT to find the max date across the columns; ORA-00904 wrong message
body: <p>I have millions of IDs and I need to find the max date from 3 different dates for each ID.<br>
  Then, I need the start date of the month of the max date.<br>
  Here's a reference:</p>
  
  <pre>
  +---------+-----------+---------------+--------------------+
  |   ID    |  SETUP_DT |  REINSTATE_DT | LOCAL_REINSTATE_DT |
  +---------+-----------+---------------+--------------------+
  | C111111 | 2018/1/1  | Null          | Null               |
  | C111112 | 2015/12/9 | 2018/10/25    | 2018/10/25         |
  | C111113 | 2018/10/1 | Null          | Null               |
  | C111114 | 2018/10/6 | 2018/12/14    | 2018/12/14         |
  +---------+-----------+---------------+--------------------+
  </pre>
  
  <p>And what I want is below:</p>
  
  <pre>
  +---------+-----------+
  |   ID    |  APP_MON  |
  +---------+-----------+
  | C111111 | 2018/1/1  |
  | C111112 | 2018/10/1 |
  | C111113 | 2018/10/1 |
  | C111114 | 2018/12/1 |
  +---------+-----------+
  </pre>
  
  <p>I try different code to get the result.</p>
  
  <p>When I used <code>case</code> and <code>unpivot</code> to find some specific IDs, the result looks all fine.</p>
  
  <pre><code>/* case */
  SELECT DIST_ID as ID, 
  trunc(
  case
      when REINSTATE_DT is not null and LOCAL_REINSTATE_DT is not null then greatest(LOCAL_REINSTATE_DT, REINSTATE_DT)
      when REINSTATE_DT is null and LOCAL_REINSTATE_DT is not null then LOCAL_REINSTATE_DT
      when REINSTATE_DT is not null and LOCAL_REINSTATE_DT is null then REINSTATE_DT
      else SETUP_DT
  end, 'MM') AS CN_APP_MON
  FROM DISTRIBUTOR
  where DIST_ID in ('CN111111','CN111112','CN111113','CN111114');
  
  /* unpivot */
  SELECT DIST_ID as ID, 
  trunc(MAX(Date_value),'MM') AS CN_APP_MON 
  FROM DISTRIBUTOR
  UNPIVOT (Date_value FOR Date_type IN (SETUP_DT, REINSTATE_DT, LOCAL_REINSTATE_DT))
  where DIST_ID in ('CN111111','CN111112','CN111113','CN111114')
  GROUP BY DIST_ID;
  </code></pre>
  
  <p>However, when I change the condition and tried to use the date period to pull out the data, the result is weird.
  To be more specific, I tried to replace </p>
  
  <pre><code>where DIST_ID in ('CN111111','CN111112','CN111113','CN111114')` &lt;br&gt;
  </code></pre>
  
  <p>by </p>
  
  <pre><code>where REINSTATE_DT
  between TO_DATE('2018/01/01','yyyy/mm/dd') and TO_DATE('2018/01/02','yyyy/mm/dd')`
  </code></pre>
  
  <p>But the unpivot function was not work. It showed:</p>
  
  <blockquote>
    <p>ORA-00904: "REINSTATE_DT": invalid identifier
    00904. 00000 -  "%s: invalid identifier"</p>
  </blockquote>
  
  <p>I want to know:</p>
  
  <ol>
  <li>Which method is more efficient, or what else more efficient way to do that?</li>
  <li>Why the unpivot method didn't work? What difference is between the 2 methods?</li>
  </ol>
  
  <p>Thank you so much!</p>