title: How do I convert an IN clause with a correlated subquery to using a WITH statement?
body: <p>I would like to convert a correlated subquery into using a WITH clause.</p>
  
  <p>Following is the query,</p>
  
  <pre><code>select count(*)
  from item_master im
  left join item_branch ib
  on im.IMLITM = ib.IBLITM
  left join supply_item si
  on im.IMLITM = si.PCLITM
  where true
  and trim(ib.IBSTKT) in ('P', '7')
  and trim(ib.IBVEND) in (
      select distinct sii.PCAN8
      from supply_item sii
      where true
      and trim(sii.PCMCU) in ('MXN09M', 'USNELP', 'USNELS')
      and sii.PCAN8 not in (60001, 60002, 60003, 60004, 60005, 60006, 60007, 60008, 60009, 60011)
      and sii.PCLITM = ib.IBLITM ---------&gt; CORRELATED SUBQUERY COMES HERE
  )
  </code></pre>
  
  <p>This is my attempt at it.</p>
  
  <pre><code>with tbl as (
  select distinct sii.PCAN8 as PCAN, sii.PCLITM as PCLITM
      from supply_item sii
      where true
      and trim(sii.PCMCU) in ('MXN09M', 'USNELP', 'USNELS')
      and sii.PCAN8 not in (60001, 60002, 60003, 60004, 60005, 60006, 60007, 60008, 60009, 60011)
  )
  select count(*)
  from item_master im
  left join item_branch ib
  on im.IMLITM = ib.IBLITM
  left join tbl
  on im.IMLITM = tbl.PCLITM
  where true
  and trim(ib.IBSTKT) in ('P', '7')
  and trim(ib.IBVEND) in tbl.PCAN
  </code></pre>
  
  <p>I get an error saying <code>no such table: tbl.PCAN</code>. I am not sure why I am going wrong. I am providing a column to be checked on for the <code>in</code> clause just like before.</p>