title: How to get the values for the next and next next date in a table and outer join with another table
body: <p>We have a table tbl with columns Alias, Effective_Date, CVal,CPrice, and another table tblA with columns Alias and Other Column</p>
  
  <p>How do we query the table tbl to return values from these 2 rows in 1 row and outer join it with another table tblA (joined on the Alias column):</p>
  
  <ol>
  <li><p>CVal (NextVal) and CPrice (NextPrice) values from row with Effective_Date the next date after somedate</p></li>
  <li><p>CVal (SecondVal) and CPrice (SecondPrice) values from row with Effective_Date the next date after the Effective_Date from #1</p></li>
  </ol>
  
  <p>For ex:</p>
  
  <pre><code>Alias   Effective_Date  CVal   CPrice
  A       01-JAN-19       1       100
  A       01-JAN-20       2       101
  A       01-JAN-21       3       102
  A       01-JAN-22       4       103
  </code></pre>
  
  <p>tblA</p>
  
  <pre><code>Alias   OtherColumn
  A       O1
  B       O2
  </code></pre>
  
  <p>Say somedate = '31-DEC-19'</p>
  
  <p>Expected result</p>
  
  <p>(the next date after '31-DEC-19' in the Effective_Date column is 01-JAN-20,</p>
  
  <p>and the next date after that is 01-JAN-21):</p>
  
  <pre><code>Alias   OtherColumn NextVal NextPrice SecondVal SecondPrice
  A         O1        2       101       3         102
  </code></pre>
  
  <p>This below query gives me error "tblA"."ALIAS": invalid identifier </p>
  
  <pre><code>select tblA.*, NextVal, NextPrice, SecondVal, SecondPrice from tblA,
  (select t.* from
    (
        lead(CVal, 1) over(order by Effective_Date) as NextVal 
       ,lead(CPrice, 1) over(order by Effective_Date) as NextPrice  
       ,lead(CVal, 2) over(order by Effective_Date) as SecondVal 
       ,lead(CPrice, 2) over(order by Effective_Date) as SecondPrice
       from tbl where Effective_Date &gt;=  '31-DEC-19' and alias = tblA.alias  --&gt; error "tblA"."ALIAS": 
       invalid identifier 
       order by Effective_Date ) t
     where rownum = 1) tblB
   where tblA.alias = tblB.alias(+)
  </code></pre>
  
  <p>Thank you</p>