title: SQL insert into using WITH statement throws an unexpected 'insert' error
body: <p>As part of my SQL query, I first create a table called 'new' based on certain conditions and then <code>insert into</code> the <code>new</code> table some columns from a different table <code>old</code> based on certain conditions. Here's a simplified version of what I am trying to do:</p>
  
  <pre><code>   item    | subitem    |   stage    |   id     | 
      -----------------------------------------------
      i1      | s1         |  picked    |    1     |
      i1      | s2         |  shipped   |    1     |
      i2      | s4         |  picked    |    2     |
      i3      | s10        | shipped    |    2     |
      i3      | s11        | eligible   |    0     |
      i4      | s2         |not eligible|    0     |
      i1      | s1         |  picked    |    3     |
      i1      | s2         |  picked    |    3     |
  </code></pre>
  
  <p>I want the output as following:</p>
  
  <pre><code>    item1|subitem1|item2|subitem2|pair_volume|item1pick|item1ship|item2pick|item2ship|
  -------------------------------------------------------------------------------------
      i1   |   s1   |  i1 | s2     | 2         |  2      | 0       | 2       | 1       |
      i1   | s1     | i2  | s4     | 1         |1        | 0       | 1       | 1       |  
      ....
      .....
      ....
  </code></pre>
  
  <p>Essentially, what I want to do here is the following:</p>
  
  <p>I want to first make a cross-join of <code>item, subitem</code> with itself, so I have all possible combinations of <code>item1</code>, <code>subitem1</code> and <code>item2</code>, <code>subitem2</code>. </p>
  
  <p>Here's how the stages are defined. If a stage is eligible, then it is just eligible, whereas if a stage is picked, then it means that item is both eligible and picked. If a stage is shipped, then it means that item is eligible, picked and shipped. Only the last stage is mentioned.</p>
  
  <p>Now, for every <code>item1, subitem1 - item2, subitem2</code> pair, I want to calculate the count of <code>id</code> sessions where this pair occurs and has stage in <code>(eligible, shipped, picked)</code> and populate the value in <code>pair_volume</code>. For eg, <code>(i1,s1)-(i1,s2)</code> pair occurs twice (once in <code>id</code> 1 and once in <code>id</code> 3) and in both these <code>id</code> sessions, both the items in the pair were <code>eligible</code> (which is implied from <code>picked</code> and <code>shipped</code>) stages. Out of the 2 times that this eligible pair occurred, how many times is item1 picked, item2 picked, item1 shipped and item2 shipped? This is what I am trying to solve. </p>
  
  <p>I can do fine until cross join. But I don't know how to insert values into the cross joined table. The real problem is more complex than what I have mentioned here. Any help is much appreciated!
  Here's the query I have so far:</p>
  
  <pre><code>with new as
  (
  select combination.item1, combination.subitem1, combination.item2, 
  combination.subitem2,
      (
      select tmp.item1, tmp.subitem1, tmp.item2, tmp.subitem2,
      case when ((tmp.item1 = tmp.item2) and 
      (tmp.subitem1 = tmp.subitem2)) then 'TRUE' else 
      'FALSE' end as indicator from
          (
          select distinct item as item1, subitem as subitem2
          from old
          cross join
          select distinct item as item2, subitem as subitem2 
          from old
          ) tmp
      ) combination
  where combination.indicator = 'FALSE'
  )
  insert into new (pair_volume)
  select count(id) as pair_volume
  from old
  where ((new.item1 = old.item) and (new.subitem1 = old.subitem) and 
  stage 
  in ('picked', 'eligible', 'shipped')) and
  ((new.item2 = old.item) and (new.subitem2 = old.subitem) and 
  stage 
  in ('picked', 'eligible', 'shipped')
  </code></pre>
  
  <p>This is essentially what I am trying to do here and the <code>insert into</code> statement keeps throwing error. The part until <code>cross join</code> works fine. But I am having trouble inserting values into the table as well as making the right conditions for the output table I want. Any help is much much appreciated!!</p>
  
  <p>However, this throws an error for me as follows:</p>
  
  <blockquote>
    <p>SQL compilation error: syntax error line 4 at position 0 unexpected 'insert'.</p>
  </blockquote>
  
  <p>I tried including a semi colon before the <code>insert into</code> statement, this is what I get:</p>
  
  <blockquote>
    <p>SQL compilation error: syntax error line 3 at position 1 unexpected ';'.</p>
  </blockquote>
  
  <p>I tried including a comma <code>,</code> before the <code>insert into</code> statement, this is what I get:</p>
  
  <blockquote>
    <p>SQL compilation error: syntax error line 4 at position 0 unexpected 'insert'. syntax error line 5 at position 0 unexpected 'select'.</p>
  </blockquote>
  
  <p>What am I doing wrong here? Based on a lot of other posts, I figured this is how we use <code>insert into</code> with a CTE.</p>