CASE statement in Netezza

Question

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.

score 0 · Answer 1 · answered Jul 29 '15 at 11:12

0

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

answered Jul 29 '15 at 11:12

The above code doesn't work. It's the same error which I got before. Why doesn't it jump to the ELSE part when the number is not in range of -32768 and 32767. – Biswajeet Ghosh Jul 29 '15 at 11:34
Sorry I forgot to say, the sql is executed on Aginity workbench. Where do you execute the SQL and which version of Netezza is being used. – Biswajeet Ghosh Jul 29 '15 at 12:19

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

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)

edited May 23 '17 at 12:13

Community

1
1

answered Jul 29 '15 at 15:58

ScottMcG

3,867
2
12
21

Hello Scott, Thanks for replying. How about the below code when I use the column from a table:SELECT CASE WHEN LENGTH(TRANSLATE(TPB.INDEX,'0123456789.-',''))!= 0 THEN NULL WHEN (INSTR(TPB.INDEX,'.',1,2)!= 0 OR INSTR(TPB.INDEX,'-',1,2)!= 0 OR INSTR(TPB.INDEX,'-',1,1) != 1) THEN NULL WHEN (TPB.INDEX != '-.') THEN NULL WHEN (CAST(TPB.INDEX AS NUMERIC(18,0)) < -32678 OR CAST(TPB.INDEX AS NUMERIC(18,0))> 32767) THEN NULL ELSE CAST(TPB.INDEX AS SMALLINT) END INDEX_BDV FROM SST_LDA_TEA2PLUSBOOLEAN TPB WHERE INDEX <> '0' – Biswajeet Ghosh Jul 30 '15 at 08:24
The column TPB.INDEX contains characters 'p' and 'b'. It throws an exception of Bad numeric input format 'b'. Please suggest – Biswajeet Ghosh Jul 30 '15 at 08:28
The CASE statement in your comment has a couple of problems, so I added an example of how I might do it from scratch in the answer. – ScottMcG Jul 30 '15 at 21:30
@Niederee Hello Scott, Thanks for replying. But the above code doesn't work for all types of character strings. For example if we the have the input string as below : '.','..','--','-','9.89.', '954-.', '99a'. I am trying to develop a robust solution which will return NULL if the input string cannot be converted to SMALLINT – Biswajeet Ghosh Jul 31 '15 at 09:13
Hello Scott, Thanks for replying. But the above code doesn't work for all types of character strings. For example if we the have the input string as below : '.','..','--','-','9.89.', '954-.', '99a'. I am trying to develop a robust solution which will return NULL if the input string cannot be converted to SMALLINT – Biswajeet Ghosh Jul 31 '15 at 09:18
I think that's different enough from the question that you originally posed that you would probably be better served by asking that as another question. As it stands this question reads to be about the evaluation for literals in a CASE statement. – ScottMcG Jul 31 '15 at 14:20
So do you think, I should make a different post or a different topic. – Biswajeet Ghosh Jul 31 '15 at 15:18
I would recommend asking an entirely new question, asking for a method of casting an arbitrary string to SMALLINT when it fits and resulting in NULL when it doesn't. – ScottMcG Jul 31 '15 at 15:27
Basically, I have Netezza at source as well as Target. In source every column is defined as NVARCHAR. While loading the data through Informatica(ETL), we have to make different type conversions such as NVARCHAR -> Timestamp, NVARCHAR -> BYTEINT/SMALLINT/INT/BIGINT and so on. We are not allowed to drop any records, so if any conversion fails we need to put a NULL for that particular conversion. Few functions available in Informatica cannot be pushed to Netezza as we have to use PUSHDOWN Optimization in Informatica. So we are trying to write views in database. – Biswajeet Ghosh Jul 31 '15 at 15:29
That's good context. Be sure to include that in the new question. – ScottMcG Jul 31 '15 at 15:32
I have started a new discussion with the topic Tpe conversion in Netezza. – Biswajeet Ghosh Jul 31 '15 at 15:48

CASE statement in Netezza

2 Answers2