Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: using text file output in query

  1. #1
    Join Date
    Dec 2006
    Posts
    11

    Default using text file output in query

    I have been trying to create a transformation that uses a query like this

    Select * from Table
    where value in ( ? )

    Where the ? is the output of a text file.

    Is this even possible? Can someone help me?

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    No.. You could have something as a text input file outputting rows and then taking that hop as input to a table input step.

    Then you could do something as:

    Select * from Table
    where value in = ?

    Where ? is then every time binded by 1 value of your input rows.

    Regards,
    Sven

  3. #3
    Join Date
    Dec 2006
    Posts
    11

    Default Close...

    That is really really close to what I want to do. Using this query:

    Select * from Table
    where value in ( ? )

    I'd what ? to be a comma delimited set of values.

    I've already written a procedure that is as you've described and it's performance is terrible. Has that been you're experience with procedures like this?

  4. #4

    Default

    I'm fairly new to the Kettle stuff but what the hell, I'm sure someone will correct me anyway!

    Could you use code from the "previous value" javascript example to concatenate the inputs together into one long comma delimited string then put a blocking step in and then use the string after that?

    One thing to watch tho is some rdbms's have a limit on the number of values in the "in" part - think it was 1024 on Oracle a while ago (9iRel2???) for example.

    Cheers,

    stevens_ns

  5. #5
    Join Date
    Dec 2006
    Posts
    11

    Default Very interesting

    Yes, I might just try your accumulate idea. Is there an example of such a script in this forum and can you point me to it?

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    @stevens_ns ... doesn't work as binding values fails for "in ( ? )" on any normal database. In Oracle you could get around by writing a PL-SQL function that converts 1 string argument to a list, but that's a pretty big detour.
    Another way would be to use variable substitution instead of ? ... something like in ( $(IN_LIST} ) but then you have to write stuff to set up the variable (in a different transformation) and you will probably hit database limits if the IN_LIST is too large.

    For an example script... anything database related is hard to give examples for that run out of the box... just a text input file (reading your arguments) connected to a table input using execute for each row and taking input from hop. And using = ? instead of IN ( ? ).

    Regards,
    Sven

  7. #7

    Default

    Yes of course, have done this dynamically in the past using TSQL and PLSQL then just executing the full SQL string, wasn't thinking prepared statements though, apols!

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

    Default

    Actually, every "in" clause can be re-written using a join.
    An IN statement typically performs very bad on a database too.

  9. #9
    Join Date
    Dec 2006
    Posts
    11

    Default Also Interesting...

    Thanks for you help on this Sven. Unfortunately my process is prohibitively long-running to use the" = ?" you recommend. That is why I was hoping to get the in ( ? ) technique to work.

    @Matt: I've never considered using a join instead of in for my problem. I'll explore that thought.

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.