Netezza Performance Server

 View Only

  • 1.  Error on CREATE OR REPLACE VIEW ....

    Posted Thu January 18, 2024 03:58 AM

    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
    ------------------------------


  • 2.  RE: Error on CREATE OR REPLACE VIEW ....

    Posted Thu January 18, 2024 04:24 AM
    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


    Original Message


  • 3.  RE: Error on CREATE OR REPLACE VIEW ....

    Posted Thu January 18, 2024 04:29 AM
    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


  • 4.  RE: Error on CREATE OR REPLACE VIEW ....

    Posted Wed January 24, 2024 04:36 AM

    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