Hi All,

on a script of View Creation we received back the following Error.

Any suggestion?

Thanks al ot in advance.

ERROR: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: CREATE OR REPLACE VIEW JMS_MV_DMF_UTENTI_TURNI AS SELECT DISTINCT DMFU.MATRICOLA, DMFU.DATA_DIM_TEMPO, DMFU.DATAINIZIO, DMFU.DATAFINE, (("NUMERIC"(TO_CHAR(DMFU.DATAFINE, 'SSSS'::"VARCHAR"), 2490386) - "NUMERIC"(TO_CHAR(DMFU.DATAINIZIO, 'SSSS'::"VARCHAR"), 2490386)) / '3600'::"NUMERIC") AS DURATA, DMFU.IDJOBTYPE, DMDU.IDPROFILO, NULL::UNKNOWN AS IDSTRUTTURA, DMDT.TURNO FROM ((JMS_DMF_UTENTI_ATT DMFU JOIN JMS_DMD_UTENTI_TURNO DMDT ON (((DMDT.MATRICOLA = DMFU.MATRICOLA) AND (TO_CHAR("TIMESTAMP"(DMDT.DATA), 'YYYY-MM-DD'::"VARCHAR") = TO_CHAR(DMFU.DATAINIZIO, 'YYYY-MM-DD'::"VARCHAR"))))) JOIN JMS_DMD_UTENTI DMDU ON ((((DMDU.MATRICOLA = DMFU.MATRICOLA) AND (DMDU.DATA_INI_VAL <= DMFU.DATAINIZIO)) AND ((DMDU.DATA_FINE_VAL >= DMFU.DATAINIZIO) OR (DMDU.DATA_FINE_VAL ISNULL)))))

.  Cause: org.netezza.error.NzSQLException: ERROR:  NUMERIC is an internal function and should not be used in SQL queries

[nz@openpsese-npshost manual_vacuum]$ nzrev
Release 11.2.1.9 [Build 195]
[nz@openpsese-npshost manual_vacuum]$

------------------------------
Andrea Ceccotti
------------------------------

Hi, the problem is that NUMERIC is an internal data type not a function. If you want to convert the column output to a numeric type use CAST instead

Best Regards,

Huw

Also, I’m not sure it makes much sense to try to convert something that’s already been converted to a character string to a number afterwards (but obviously don’t know the data or use case)

Huw


Original Message:
Sent: 1/18/2024 4:24:00 AM
From: Huw Ringer
Subject: RE: Error on CREATE OR REPLACE VIEW ....

Hi, the problem is that NUMERIC is an internal data type not a function. If you want to convert the column output to a numeric type use CAST instead

Best Regards,

Huw

Hi ALl,

Being a Forum, I am pleased to participate in information sharing, so I report here below, the solution provided by IBM Support (we opened a PMR) on this Issue.

ERROR:  NUMERIC is an internal function and should not be used in SQL queries

For this one we need to replace netezza binaries what means outage to stop DB,

Steps are simple, we will swap 2 files and restart db.

The issue is NUMERIC in Netezza is both datatype AND a function. The bug is that it doesn't allow you to run NUMERIC

Thanks all.

Bye

------------------------------
Andrea Ceccotti
------------------------------

Original Message:
Sent: Thu January 18, 2024 04:28 AM
From: Huw Ringer
Subject: Error on CREATE OR REPLACE VIEW ....

Also, I'm not sure it makes much sense to try to convert something that's already been converted to a character string to a number afterwards (but obviously don't know the data or use case)

Huw



Original Message:
Sent: 1/18/2024 4:24:00 AM
From: Huw Ringer
Subject: RE: Error on CREATE OR REPLACE VIEW ....

Hi, the problem is that NUMERIC is an internal data type not a function. If you want to convert the column output to a numeric type use CAST instead

Best Regards,

Huw