title: Case statement for 'where [value] is not null'
body: <p>I've seen plenty of SO questions where someone is testing for a NULL value within a case statement, but nothing of the nature of this question. I want the return of the case statement to be a 'is not null' expression for a where clause. </p>
  
  <p>This is what I have:</p>
  
  <pre><code>INSERT INTO table1 (Id) (
       SELECT DISTINCT AId AS Id
       FROM table2 As t2
       INNER JOIN table3
       ON (
       ...
       )
       WHERE CASE
            WHEN test_value &lt;&gt; '' THEN (another_value IS NOT NULL) 
       END
  )
  </code></pre>
  
  <p>This gives the error: </p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'is'.</p>
  </blockquote>
  
  <p>In short, if the test value is not empty-string, then I do not want rows with another_value=NULL in my table1.</p>
  
  <p>My program needs to pass 2 cases</p>
  
  <pre><code>Case 1:
  Row#     test_value     another_value    ....
  1        'tst'          '919'
  2        'tst'           NULL
  ONLY ROW ONE SHOULD BE INSERTED
  
  Case 2:
  Row#     test_value     another_value    ....
  1        ''             '919'
  2        ''              NULL
  BOTH ROWS SHOULD BE INSERTED
  </code></pre>