title: Powershell select where not in
body: <p>I have a Powershell script where I select all the values (they are numeric) from the column of a tab in an Excel sheet. It works but I want to exclude numbers that are returned from a select from an Oracle table. For the moment I just want to get the select "where not in" functioning, I will worry about putting the values I want excluded into an array later.
  My Powershell select is below</p>
  
  <pre><code>$Table = "Inventory Parts$"
  $qry = "select [Part No] from [{0}]" -f $Table;
  $cmd.CommandText = $qry;
  $da.SelectCommand = $cmd;
  $dt = new-object System.Data.dataTable("$($Table)");
  $null = $da.fill($dt);
  </code></pre>
  
  <p>I select from the tab (Inventory Parts) and assign it to a data table. How do I put in "where not in" to the select? If I could just have an array hardcoded with values and use that as the values for "where not in" it would be start. After that I will instead populate the array from the Oracle table.
  Thank you for any replies.</p>
  
  <p>Edit:
  I have got as far as populating an array with the values I want in the "where not in" clause (see below).</p>
  
  <pre><code>$queryString = "select PART_NO  from INVENTORY_PART_CFV WHERE PART_NO like 
  '100009%'   "
  
     $array = New-Object System.Collections.ArrayList
  
  
     $command = new-Object 
      System.Data.OracleClient.OracleCommand($queryString, $connection)
  
    $connection.Open()
  
  
    $rdr = $command.ExecuteReader();
  
    Foreach($row in $rdr)
  
     {
  
  
    $array.add(  $row.Item("PART_NO") ) 
  
     }
  </code></pre>
  
  <p>They go into the array "$array" but I need to append this on to my select statement</p>
  
  <pre><code> "select PART_NO  from INVENTORY_PART_CFV WHERE PART_NO NOT IN " + $array
  </code></pre>
  
  <p>I don't know how to do this though.</p>