'CASE statement in Netezza

I am new to Netezza so may be I am not able to figure out the issue.

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.

The scenario is as below :

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 /!\

I am trying to build a view. I tried building test query for conversion to smallint as below:

SELECT CASE WHEN CAST('99999' AS NUMERIC(18,0)) >= -32678 AND
                 CAST('99999' AS NUMERIC(18,0)) <= 32767
            THEN CAST('99999' AS smallint)
            ELSE NULL END

But everytime it fails with the error msg as below :

*ERROR [HY000] ERROR:  pg_atoi: error reading "99999": Numerical result out of range */

I tried some other alternative as below :

SELECT CASE WHEN CAST('99999' AS NUMERIC(18,0)) >= -32678 AND
                 CAST('99999' AS NUMERIC(18,0)) <= 32767
            THEN 'A'
            ELSE NULL END

The result is NULL. but for the above case it doesn't return NULL, rather it returns an exception.

castingcasenetezza


Solution 1:[1]

Your query work properly, the problem isn't in the query. Show us another code. You can use simpler form of query.

SELECT CASE WHEN CAST('99999' AS NUMERIC(18,0)) between -32678 AND 32767
            THEN CAST('99999' AS smallint)
            ELSE NULL 
       END

Solution 2:[2]

You first query is failing because the system cannot CAST 99999 as a SMALLINT, which only covers ranges -32678 from 32767. The CAST of a literal will be evaluated at compilation time and will never make it to runtime in order to evaluate the CASE logic. This is probably what is confusing you here.

SELECT CASE WHEN CAST('99999' AS NUMERIC(18,0)) >= -32678 AND
                 CAST('99999' AS NUMERIC(18,0)) <= 32767
            THEN CAST('99999' AS smallint)
            ELSE NULL END

If you test this against data in an actual table it will perform as you expect.

TESTDB.ADMIN(ADMIN)=> create table smallint_test (col1 varchar(10));
CREATE TABLE
TESTDB.ADMIN(ADMIN)=> insert into smallint_test values ('99999');
INSERT 0 1
TESTDB.ADMIN(ADMIN)=> insert into smallint_test values ('1');
INSERT 0 1
TESTDB.ADMIN(ADMIN)=> SELECT COL1,
   CASE
      WHEN CAST(COL1 AS NUMERIC(18,0)) >= -32678
      AND CAST(COL1 AS  NUMERIC(18,0)) <= 32767
      THEN CAST(COL1 AS SMALLINT)
      ELSE NULL
   END
FROM SMALLINT_TEST;

 COL1  | CASE
-------+------
 1     |    1
 99999 |
(2 rows)

Based on your additional comments, I think the answer from @Niederee to this question can help you out.

Using the TRANSLATE function as he describes, you could do this:

SELECT INDEX,
   CASE
      WHEN
         TRANSLATE(INDEX,'0123456789','') IN ('','.','-','-.')
      THEN
         CASE
            WHEN INDEX BETWEEN -32678 AND 32767
            THEN INDEX::SMALLINT
            ELSE NULL
         END
      ELSE NULL
   END THE_NUMBER
FROM TPB;

 INDEX | THE_NUMBER
-------+------------
 1     |          1
 99999 |
 p     |
 p99   |
(4 rows)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Community

Related Questions