title: Teradata CASE WHEN attribute IN SELECT
body: <p>I have 2 tables in Teradata - "a" and "ch". Table ch contains 2 colums "amt" and "code". Between tables "a" and "ch" is LEFT JOIN. Join is made, and in the SELECT part I am trying to SUM amt values. But when a "code" attribute has a speific values it hast to take only 70% of the "amt" value.</p>
  
  <p>This is what I have tried:</p>
  
  <pre><code>SELECT SUM 
         (
         CASE WHEN ch.code IN (SELECT code from ...)
            then 0.7*ch.amt
         )   
         else ch.amt
         END
  FROM a LEFT JOIN ch
  </code></pre>
  
  <p>I get an error:</p>
  
  <blockquote>
    <p>Illegal expression in WHEN Clause of CASE expression.</p>
  </blockquote>
  
  <p>Google says, that it is because <code>CASE</code> does not allow <code>SELECT</code> statements.</p>
  
  <p>Any suggestion how can I achieve the above described functionality?</p>