Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Sql Error

  1. #1
    Join Date
    Feb 2011
    Posts
    240

    Default Sql Error

    Hi,

    I've a trasformation with a Table input, in the tab Sql of Table input my query is:

    SELECT
    CARTELLA.JDBINTERF_VERSION
    , CARTELLA.JDBINTERF_LASTCNG
    , CARTELLA.N_CARTELLA
    , COGNOME || NOME as "COGNOME_NOME"
    , CARTELLA.DATA_NASC as "DATA_NASCITA"
    , ETA
    , SESSO
    , COD_COM_NASC as "COD_COMUNE_NASCITA"
    , COMUNI.DESCRIZIONE as "COMUNE_NASCITA"
    , CITTADINANZA as "COD_CITTADINANZA"
    , DES_CITTADIN as "CITTADINANZA"
    , CARTELLA.COD_REG as "CODICE_SANITARIO"
    , CARTELLA.COD_FISC as "CODICE_FISCALE"
    , CARTELLA.COD_USL as "CODICE_USL"
    , DATA_APERTURA
    , DATA_CHIUSURA
    , TAB_DESCRIZIONE as "MOTIVO_CHIUSURA"


    FROM SINSNT_TEST.CARTELLA, SINSNT_TEST.CARTELLA_ETA, SINSNT_TEST.COMUNI, SINSNT_TEST.CITTADIN, SINSNT_TEST.TAB_VOCI

    WHERE CARTELLA.N_CARTELLA=CARTELLA_ETA.N_CARTELLA and CARTELLA.COD_COM_NASC=COMUNI.CODICE and CARTELLA.CITTADINANZA=CITTADIN.CD_CITTADIN
    and CARTELLA.MOTIVO_CHIUSURA=TAB_VOCI.TAB_VAL and TAB_VOCI.TAB_COD= 'MCHIUS'


    But when I click on Preview, I've this error:

    2011/10/07 10:51:45 - sins_anagra.0 - , TAB_DESCRIZIONE as "MOTIVO_CHIUSURA"


    FROM SINSNT_TEST.CARTELLA, SINSNT_TEST.CARTELLA_ETA, SINSNT_TEST.COMUNI, SINSNT_TEST.CITTADIN, SINSNT_TEST.TAB_VOCI

    WHERE CARTELLA.N_CARTELLA=CARTELLA_ETA.N_CARTELLA and CARTELLA.COD_COM_NASC=COMUNI.CODICE and CARTELLA.CITTADINANZA=CITTADIN.CD_CITTADIN
    2011/10/07 10:51:45 - sins_anagra.0 - and CARTELLA.MOTIVO_CHIUSURA=TAB_VOCI.TAB_VAL and TAB_VOCI.TAB_COD= 'MCHIUS'
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Errore inatteso :
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : An error occurred executing SQL:
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : SELECT
    CARTELLA.JDBINTERF_VERSION
    , CARTELLA.JDBINTERF_LASTCNG
    , CARTELLA.N_CARTELLA
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , COGNOME || NOME as "COGNOME_NOME"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , CARTELLA.DATA_NASC as "DATA_NASCITA"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , ETA
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , SESSO
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , COD_COM_NASC as "COD_COMUNE_NASCITA"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , COMUNI.DESCRIZIONE as "COMUNE_NASCITA"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , CITTADINANZA as "COD_CITTADINANZA"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , DES_CITTADIN as "CITTADINANZA"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , CARTELLA.COD_REG as "CODICE_SANITARIO"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , CARTELLA.COD_FISC as "CODICE_FISCALE"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , CARTELLA.COD_USL as "CODICE_USL"
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , DATA_APERTURA
    , DATA_CHIUSURA
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : , TAB_DESCRIZIONE as "MOTIVO_CHIUSURA"


    FROM SINSNT_TEST.CARTELLA, SINSNT_TEST.CARTELLA_ETA, SINSNT_TEST.COMUNI, SINSNT_TEST.CITTADIN, SINSNT_TEST.TAB_VOCI

    WHERE CARTELLA.N_CARTELLA=CARTELLA_ETA.N_CARTELLA and CARTELLA.COD_COM_NASC=COMUNI.CODICE and CARTELLA.CITTADINANZA=CITTADIN.CD_CITTADIN
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : and CARTELLA.MOTIVO_CHIUSURA=TAB_VOCI.TAB_VAL and TAB_VOCI.TAB_COD= 'MCHIUS'
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : ORA-01722: numero non valido

    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.openQuery(Database.java:1864)
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:221)
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:130)
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2011/10/07 10:51:45 - sins_anagra.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : java.lang.Thread.run(Thread.java:619)
    2011/10/07 10:51:45 - sins_anagra.0 - Finished reading query, closing connection.

    Why?

    Thanks

    Mariarita

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi Mariarita,

    please test the queries you got in some Oracle development environment. It may give you better error reporting. In this case Oracle is complaining about "ORA-01722: numero non valido", which may be caused by a lot of things. My guess is that it's related to the comparisons you do in the where clause.

    Cheers
    Slawo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.