Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Table Input SQL error

  1. #1

    Default Table Input SQL error

    Hello everyone,
    I am facing a weird issue with pentaho Table input , where i run a query for lookup for a value and suppose if no records are found in the database it gives error stating sql error query is not proper but if the record is found it is not giving error

    Have anyone ever encountered any problem like this ??

    Regards,
    Diwakar Chittora

  2. #2

    Default

    Hi Matt,
    Could you please let me know how to fix the issue of handling null value in table input if query returns null while lookup.

    Please find the example below:
    I have a query like this
    select value_id from tablename where col1 = ? and col2 = ?

    suppose while lookup if it doesn't find any matching record in the database then value_id is null and pentaho is giving error in this ,
    it says there is some sql error.
    If i run the same query and while database lookup if i get any value for value_id i wont get any error and it works fine.

    I am not able to fix this please let me know how to fix it .

    Please let me know if you need more information on the same . If you want i can provide you the transformation also

    thanks in advance ,

    Regards,
    Diwakar
    Last edited by chittora; 06-17-2009 at 02:09 PM.

  3. #3
    Join Date
    Feb 2009
    Posts
    321

    Default

    please post the sql error....
    if you are working with oracle try with the function NVL in the step Table Input...... example:

    NVL(value_id,'value_null') ..


    here, valueNull is a default value if value_id is null then catch this value in a java Script

  4. #4

    Default

    hello hernanthiebaut,
    Thanks for your reply ,
    Please find the sql error i am getting :
    2009/06/19 15:10:48 - METADATA.0 - Finished processing (I=0, O=0, R=221, W=1, U=1, E=0)
    2009/06/19 15:10:48 - ReadFLMDData.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Unexpected error :
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : org.pentaho.di.core.exception.KettleDatabaseException:
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : An error occurred executing SQL:
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : select COALESCE(ID,0) from X.T_VF c, X.VHID v where v.VID= ? and v.VID = c.VID and c.VF_FROM<=? and (c.VF_TO is NULL or c.VF_TO<= ?)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1782)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:214)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:127)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:343)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.c.kh.c(kh.java:1660)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.c.kh.a(kh.java:1224)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.b.db.n(db.java:737)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.b.db.i(db.java:257)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.b.db.c(db.java:53)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.b.t.c(t.java:46)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.b.sb.g(sb.java:154)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.c.kh.o(kh.java:1219)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.c.lh.d(lh.java:2436)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.c.lh.d(lh.java:2507)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.c.lh.S(lh.java:432)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.ibm.db2.jcc.c.lh.executeQuery(lh.java:415)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1743)
    2009/06/19 15:10:48 - fetching_ID.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : ... 4 more
    2009/06/19 15:10:48 - Fetching_VIN_Id.0 - Finished processing (I=0, O=0, R=1, W=2, U=2, E=0)
    2009/06/19 15:10:48 - fetching_ID.0 - Finished reading query, closing connection.
    2009/06/19 15:10:48 - Join.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=0)
    2009/06/19 15:10:48 - input_Arguments.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/06/19 15:10:48 - Select_XML_Values.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/06/19 15:10:48 - dummyCounter1.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/06/19 15:10:48 - fetching_ID.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2009/06/19 15:10:48 - ETL_LOAD_METADATA_Combined - Transformation detected one or more steps with errors.
    2009/06/19 15:10:48 - ETL_LOAD_METADATA_Combined - Transformation is killing the other steps!
    2009/06/19 15:10:50 - Spoon - The transformation has finished!!

    =======
    I tried using COALESCE which is equivalent to NVL in Oracle but still it is giving error.

    I guess nvl works when there is a record and the corresponding column value is NULL but in my case there is no corresponding record found on the basis of search criteria in the database .

    Please provide your inputs for the same

    Thanks ,
    Diwakar

  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The error comes from DB2, not from PDI per-se.
    That being said, it would not hurt to take a look at the JDBC driver to see if you need to update that. Usually taking the version identical to the one from the server is a good approach. YMMV as usual.

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.