Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Generate dynamic IN clause from rows

  1. #1

    Default Generate dynamic IN clause from rows

    I thought I had seen a way to do this in the past from a PDI step, but I can't find it, and I couldn't find anything relevant on the forums after searching for a bit.

    I have a case where I want to select a set of IDs in a Table Input step, and then compress the output rows in to a comma-delimited list of IDs. I will then pass this comma-separated list of IDs to another Table Input step to fill in an IN clause, i.e.

    SELECT columns
    FROM tableName
    WHERE id IN (${dynamicListOfIds})


    Is it possible to compress rows in to a single comma-separated field like this? If so, what's the best way to do it?

    Thanks,

    - max

  2. #2
    Join Date
    Mar 2006
    Posts
    170

    Default I've done this before too

    Hi,

    I'm certain there is a better way to achieve this (1000 ways to skin a cat thing)...

    I had a JS step that caught the rows and created a ',' del string. It new when the last row was pushed through and would then push out the newly created string, set it to a VAR and next Trans would have the Table Input step ref-ing the VAR like you have.

    It worked... but not too pretty.

    If the DEL list will be huge you might be better off putting whatever was going to be in the DEL list in a TEMP TBL if you can and doing the Table Input as one stmt having your SQL do a WHERE FOO IN (SELECT * FROM TEMP_TBL).

    Kent

  3. #3
    Join Date
    Sep 2007
    Posts
    834

    Default

    Quote Originally Posted by maxim.porges View Post
    compress the output rows in to a comma-delimited list of IDs.
    you do this with a group by step.

    Quote Originally Posted by maxim.porges View Post
    then pass this comma-separated list of IDs to another Table Input step to fill in an IN clause, i.e.
    you can receive parameters from previous steps by checking "insert data from step" and selecting the source step. In this case the proper syntax in the sql statement would be ?, not ${}

  4. #4

    Default

    Thanks guys. I will try out the Group By step and see what I get.

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.