Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Dimension Lookup/Update, sql errors

  1. #1

    Default Dimension Lookup/Update, sql errors

    I am trying to use the Dimension Lookup/Update to change a field in a table. I have not used it before and just wanted to do a simple test to see it working, ie version number increasing etc.

    I am getting the following error:
    2009/07/13 13:50:10 - Dimension lookup/update.0 - Starting to run...
    2009/07/13 13:50:10 - Dimension lookup/update.0 - Dimension Lookup setting preparedStatement to [SELECT techKey, ver, name AS n FROM Slowly.sourcedata WHERE i = ? AND ? >= from
    2009/07/13 13:50:10 - Dimension lookup/update.0 - AND ? < to
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Unexpected error :
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Unable to prepare dimension lookup
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : [SQLCODE: <-12>:<A term expected, beginning with one of the following: identifier, constant, aggregate, $$, :, (, +, -, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %SQLUPPER, %STRING, or %UPPER>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : [Cache Error: <<SYNTAX>errdone+3^%qaqqt>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : [Location: <Prepare>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : [%msg: < A term expected, beginning with either of: identifier, constant, aggregate, $$, (, :, +, -, %ALPHAUP, %EXACT, %MVR %SQLSTRING, %SQLUPPER, %STRING. or %UPPER^SELECT techKey , ver , name AS n FROM Slowly . sourcedata WHERE i = :%qpar(1) AND :%qpar(2) >= from>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.setDimLookup(DimensionLookup.java:773)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:197)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2889)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.run(DimensionLookup.java:1591)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Caused by: java.sql.SQLException: [SQLCODE: <-12>:<A term expected, beginning with one of the following: identifier, constant, aggregate, $$, :, (, +, -, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %SQLUPPER, %STRING, or %UPPER>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : [Cache Error: <<SYNTAX>errdone+3^%qaqqt>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : [Location: <Prepare>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : [%msg: < A term expected, beginning with either of: identifier, constant, aggregate, $$, (, :, +, -, %ALPHAUP, %EXACT, %MVR %SQLSTRING, %SQLUPPER, %STRING. or %UPPER^SELECT techKey , ver , name AS n FROM Slowly . sourcedata WHERE i = :%qpar(1) AND :%qpar(2) >= from>]
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.CacheConnection.getServerError(CacheConnection.java:1057)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.CacheConnection.processError(CacheConnection.java:1276)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.InStream.readHeader(InStream.java:159)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.CachePreparedStatement.prepareInternal(CachePreparedStatement.java:539)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.CachePreparedStatement.prepare(CachePreparedStatement.java:508)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.CachePreparedStatement.<init>(CachePreparedStatement.java:110)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.CacheConnection.prepareStatement(CacheConnection.java:1440)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.intersys.jdbc.CacheConnection.prepareStatement(CacheConnection.java:370)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.setDimLookup(DimensionLookup.java:760)
    2009/07/13 13:50:10 - Dimension lookup/update.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ... 3 more


    is this a problem with the database or some issue with kettle?, or any pointers as to where to look would be apprecaited. I'm running version 3.2.

  2. #2
    pstoellberger Guest

    Default

    which db are you using?
    this is the prepared statement:
    SELECT techKey, ver, name AS n FROM Slowly.sourcedata WHERE i = ? AND ? >= from AND ? < to

    try to replace the ? with some possible values and run it in your favourite db query tool and see what it says

    i suspect the "? >= from" though ("FROM" !!)
    there are several names you shouldn't be using as column names in sql. this includes "name" (works mostly, but its a key word), "from", "to", ...

    -paul

  3. #3

    Default

    Quote Originally Posted by pstoellberger View Post
    which db are you using?
    this is the prepared statement:
    SELECT techKey, ver, name AS n FROM Slowly.sourcedata WHERE i = ? AND ? >= from AND ? < to

    try to replace the ? with some possible values and run it in your favourite db query tool and see what it says

    i suspect the "? >= from" though ("FROM" !!)
    there are several names you shouldn't be using as column names in sql. this includes "name" (works mostly, but its a key word), "from", "to", ...

    -paul
    I changed my field names to non keyword values and it works now.
    Cheers, didn't think about that.

  4. #4
    pstoellberger Guest

    Default

    no problem
    glad i could help

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.