Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Help with field substitution on table input

  1. #1
    Join Date
    Feb 2012
    Posts
    16

    Default Help with field substitution on table input

    Hi guys I'm looking for help with field substituion on table input...

    I have a transformation that will give rows to an input table like this:
    Name:  2014-06-11 11_32_18-Examine preview data.jpg
Views: 169
Size:  26.2 KB

    I wanted to use these rows on a query like this:
    Code:
    WITH T1 AS(
      SELECT FIELDS FROM TABLE1 WHERE CAMPANHA = ? AND ESTADOS_OK IN ?
    ),
    T2 AS (
      SELECT OTHER FIELDS FROM TABLE2
    )
    
    SELECT * FROM T1,T2 WHERE T1.PK = T2.PK
    If I directly place the fields on a table input, the query will work, but when I'm using the "Insert data from step" with "Execute for each row?" checked it doesn't...

    So far the log seems fine (attached), showing my arguments, but I get the "Sorry, during preview there weren't any rows to display for this step" when previewing the table input.

    I think this happens because I'm using the IN, I was wondering if I'm doing something wrong or just shouldn't be able to use field substituition like this (passing a whole string).

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

    Default

    You can't use a list expression (1, 2, 3) as a SQL variable (?) in a prepared statement.
    That's SQL law, not a Kettle restriction.
    Use Kettle variables like ${LIST} for this.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Feb 2012
    Posts
    16

    Default

    Oh I thought it would work like "Execute SQL script" with "Bind parameters?" unchecked, doing a literal replacement...

    I'll work my way around, thanks

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.