Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: variable or field replacement in table input

  1. #1

    Default variable or field replacement in table input

    Hi folks, what am I doing wrong ...:
    I tried to use for test purposes the add constants step but in specific I require to use the variable within the sql select.
    I have one sql script step before (deleting the old data), which works as expected, but I can't achieve the same for the table input step:
    ? or ${} are not recognized or translated within the where clause.
    I think the behaviour of the table input step is a bit strange, if I use the "show input fields" I can't see the input fields from the "add constants" step or of the "get variable" step, if I select them within the table input step as the "insert data from step". If I don't select them, I can see the input fields, otherwise Kettle tells me, there are no input fields.
    I have tried to enable and disable the switch for "replace variables".
    Anything else, what I'm not aware of?
    I'm using PDI 3.1 GA.
    Tx. Alexander

    WHERE

    version_id=1
    AND

    conslevel_id=1
    AND

    period_id
    between 480 and 491 and

    company_id = ${COMPANY_ID};

    OR I USED
    for the first constant a ? and the second was not used, but in my understanding would be also a ?.

  2. #2
    pstoellberger Guest

    Default

    A screenshot or even better, the transformation as an attachement would help to solve that problem

    You must set variables in a previous transformation if you want to use them for the table input step.. did you do that?

  3. #3

    Default ktr file - here we go!

    enclosed the ktr file, which uses a disabled "get variable" and for testing purposes an enabled add constant step - or vice versa ...
    Attached Files Attached Files

  4. #4
    pstoellberger Guest

    Default

    You need to set the variables in an earlier transformation, as i said.. This transformation could look like the one i attached
    So you put this transformation in front of the other in a job.. and in the second transformation you don't need get variable or add constant or whatever for the table input step. ".... company_id = ${COMPANY_ID};" should be fine then.

    -paul
    Attached Files Attached Files

  5. #5

    Default

    Hi Paul,
    I have a separate transformation for the setting of the variables in place and in use by other transformations. There it works fine, also within the sql script it works fine. But this is not the case for the table input step!
    If I run a test with the step for constants I rcv. an error:
    Expected to read parameters from step [Add constants for testing: company_id and schema] but none were found.

    If I use the variable version, assigning the variable with a get variable step, the message is similar to:
    no variable, probably get variable step is missing or you are in preview mode

    If I use the variable version without the variable step:
    Expected to read parameters from step [modify company_id to be integer] but none were found.
    Cheers, Alexander

  6. #6

    Default

    Now I forced kettle to display again the message I was looking for:

    Unable to find rowset to read from, perhaps step [Get Variables] doesn't exist. (or perhaps you are trying a preview?)

    So you can ignore the message I posted before with "similar to ..."

    My feeling is, there is a bug with the table input step. Does anybody use the combination of a field from a preceeding step or with a variable with 3.1?

    I don't think I'm so wrongly, because it even does not work with the add constant step. At least this should work!

    Cheers,
    Alexander

  7. #7
    pstoellberger Guest

    Default

    ok now i tried it myself
    the attached transformation works

    you could replace the "get variables" step with a "generate rows" step to produce an input for the table input, but don't forget to tell you want only 1 row generated.. not 10 (additionally don't forget to tick "insert data from step" and choose generate rows/get varaibles)

    -paul
    Attached Files Attached Files

  8. #8

    Default

    Paul, great and thanks a lot, I#ve just run a test, this seems to be the missing link. Weird in my opinion, but it works ...
    Cheers,
    Alexander

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.