Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Table Input with Oracle and values from previous step fails

  1. #1

    Default Table Input with Oracle and values from previous step fails

    Hi,

    I have a Table Input step that is reading data from a Postgres database and returning just a single column and row in a string format

    2017-11-30 15:29:02

    I want to pass that value on to the next Table Input Step that is reading data from an Oracle database and the query is

    Code:
    SELECT name
    FROM customer
    where tmp_created >= TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')
    But I get this error message back

    Code:
    2017/11/30 14:38:45 - Table input 2.0 - SELECT name
    FROM customer
    where tmp_created >= TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')
    2017/11/30 14:38:45 - Table input 2.0 - ORA-30088: datetime/interval precision is out of range
    To me it looks like as if the ? is not replaced with the value from the incoming data stream.

    Both Table Input steps are connected via Hop, 'Insert data from step' in 'Table Input 2.0' is set to 'Table Input' and Execute for each row is set to YES.
    What am I missing here?!
    PDI 7
    Postgres
    Windows 7

  2. #2
    Join Date
    Nov 2009
    Posts
    688

    Default

    Your settings looks ok.
    Is the query
    SELECT name from customers
    where tmp_created >= TO_TIMESTAMP('2017-11-30 15:29:02','YYYY-MM-DD HH24:MI:SS') working
    Because the message datetime/interval precision is out of range looks like tmp_created has a different length

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    @Bobse: Is it a fact that the Postgres Timestamp is returned as a String? I mean did you check the metadata?
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by marabu View Post
    @Bobse: Is it a fact that the Postgres Timestamp is returned as a String? I mean did you check the metadata?
    It is a fact that Postgres Timestamp is returned as a Timestamp ... but the substitution is tricky ... I would cast it:

    select name
    from customer
    where tmp_created>=cast('?' as timestamp)

    PS. Assuming of course a simple tmp_created>=? doesn't work ... which I would test as my first option.
    Last edited by DepButi; 12-01-2017 at 07:50 AM. Reason: Added comment

  5. #5

    Default

    Bloody *#!* ...

    Found the issue ...
    my SELECT also included

    Code:
    SELECT name
    , current_timestamp()
    FROM customer
    where tmp_created >= TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')
    and that is what messed it up. current_timestamp() needs to be current_timestamp(0) to make it work ...
    Thanks for all your input!

    Bobse
    PDI 7
    Postgres
    Windows 7

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.