Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: AddSequence start at value 6610570460000000 gives error ORA-00904: invalid column

  1. #1

    Default AddSequence start at value 6610570460000000 gives error ORA-00904: invalid column

    Hello,

    I am trying to Insert/Update 10000 rows of input into a table which has a unique key 16 digits long, I am trying to use 6610570460000000 in Add Sequence for the "key value" and then add 1 to it.
    This value is not in the database so all 10000 rows should be added.
    but I get this error

    Error looking up row in database
    ORA-00904: invalid column name

    Is there a problem using a 16 digit Add Sequence ?

  2. #2
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Quote Originally Posted by rdmclure View Post
    Error looking up row in database
    ORA-00904: invalid column name
    Rod,

    Double check your configuration - it's saying that the column you are using for your key value does not exist in the database, so it can't look the row up.

  3. #3

    Default

    it is a very simple transformation
    there is an excel input with 10,000 rows
    an add sequence with name valuename

    an insert/update with
    The key(s) to look up the value(s) table field stream field
    GLB_DTIME = valuename

    (these are both drop downs that I chose the values from, the only place to get them is the database connection or the stream)

    clicking Get Update Fields returns
    Table field Stream field Update
    GLB_DTIME valuename
    ACTIV ACTIV
    BRANCH BRANCH
    CLK_ID CLK_ID
    GLB_REPORT GLB_REPORT
    ...
    the only thing different is the Add Sequence default maximum is no where near 16 digits

    Thanks

  4. #4
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Perhaps if you turn your logging level up, it might be able to tell you which DB Field it is trying to look up...

    Also, if you do a preview rows from your add sequence, do you get values in your "valuename" field?
    Your select statement might be being generated as
    SELECT * FROM tbl where GLB_DTIME =
    Which Oracle could be complaining about...
    Last edited by gutlez; 04-13-2012 at 01:23 PM.

  5. #5

    Default

    if I preview 1000 rows I get a few rows (21) returned with the valuename and then the error message

    the sql definitely looks like
    SELECT * FROM tbl where GLB_DTIME =

  6. #6
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    That's really odd, because the sql that should be generated if everything is working normally should be more like:
    SELECT * FROM tbl where GLB_DTIME = ?

  7. #7

    Default

    yes the ? is there

  8. #8
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    That's about as far as I can take you, since I lack an Oracle system.
    If you turn the logging up to row-level, it should tell you what it thinks the error is.

    Question: What type is the column GLB_DTIME? Oracle might be trying to do a Cast in the middle, and not happy with it.

  9. #9

    Default

    I tried it again with an Add sequence starting at 1
    and I recreated my Insert/Update
    and it worked fine.

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.