title: CASE statement in Netezza
body: <p>I am new to Netezza so may be I am not able to figure out the issue.</p>
  
  <p>I have a scenario to implement in informatica with Netezza as the database. As few functions are not available/supported by informatica, so decided to make some netezza views and use them in informatica.</p>
  
  <p>The scenario is as below :</p>
  
  <pre><code>INDEX_BDV = Convert "SST_LDA_TEA2PLUSBUCKET.INDEX" from CHAR to SMALLINT
  /!\ If conversion fails, do not reject the records but put a NULL as default value /!\
  </code></pre>
  
  <p>I am trying to build a view. I tried building test query for conversion to smallint as below:</p>
  
  <pre><code>SELECT CASE WHEN CAST('99999' AS NUMERIC(18,0)) &gt;= -32678 AND
                   CAST('99999' AS NUMERIC(18,0)) &lt;= 32767
              THEN CAST('99999' AS smallint)
              ELSE NULL END
  </code></pre>
  
  <p>But everytime it fails with the error msg as below :</p>
  
  <pre><code>*ERROR [HY000] ERROR:  pg_atoi: error reading "99999": Numerical result out of range */
  </code></pre>
  
  <p>I tried some other alternative as below :</p>
  
  <pre><code>SELECT CASE WHEN CAST('99999' AS NUMERIC(18,0)) &gt;= -32678 AND
                   CAST('99999' AS NUMERIC(18,0)) &lt;= 32767
              THEN 'A'
              ELSE NULL END
  </code></pre>
  
  <p>The result is NULL. but for the above case it doesn't return NULL, rather it returns an exception.</p>