title: oracle sql - define date as variable in a with statement
body: <p>in oracle - i am trying to figure out how to define variables in a with statement.</p>
  <p>when i define a variable as a number it works fine:</p>
  <pre><code>with a as(
      select 100 as query_rows
      from dual
  )
  
  ,b as (
      select * from table1 where rownum=query_rows
  )
  
  select * from b --working great
  </code></pre>
  <p>however,if i want to define a date as a variable,i keep getting an error:</p>
  <pre><code>with a as(
      select DATE '2020-10-01' as query_date
      from dual
  )
  
  ,b as (
      select * from table1 where table1.date=query_date
  )
  
  select * from b --  ORA-00904 : &quot;query_date&quot; is not a valid identifier
  </code></pre>
  <p>from oracle :ORA-00904</p>
  <blockquote>
  <p>You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.</p>
  </blockquote>
  <p>so,why does the first query work and the second one doesn't?</p>