Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Passing string parameter to Table Input

  1. #1
    Join Date
    Mar 2014
    Posts
    5

    Default Passing string parameter to Table Input

    I am new to Pentaho and have the following issue:

    I have an SQL statement (Inside a Table Input step) defined as follows:

    SELECT arc_id, place_id
    FROM ARC a
    WHERE EXISTS (SELECTnull
    FROM TRACK t WHERE EXISTS (SELECTnull
    FROM PLATFORM_TRACK pt
    WHERE pt.track_id = t.track_id
    AND t.place_id = ?
    AND pt.platform_no = ?)
    AND a.track_id = t.track_id
    AND a.reverse = 'T')

    The script takes two parameters, an integer (place_id) and a string (pfm_no).

    Prior to this step I am using a Select/Renames to define my two input parameters. Everthing executes okay but the SQL query returns nothing when I am expecting something!

    Removing the second parameter and replacing with '4' (or '4 ') in the query returns a number of values!

    Removing the first parameter and replacing with 205118 I get no values, which leads me to suspect a problem with passing string types into the query.

    pfm_no is originally read from an xml file as a string (with trim type 'both')

    Any ideas what is happening?


    For info:

    I have looked at the output from the Select/Renames and get
    PLACE_ID;pfm_no
    205118;4
    205118;3
    205119;5
    etc.

    So the input to the SQL looks good.

    The SQL script executed in SQL Developer (with place_id=205118 and pfm_no='4') returns
    Arc_id = 4522829, Place_id=205118

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Without seeing the transform, I can't say for certain, but could place_id be being read as a string?

    I ask because
    WHERE pt.track_id = t.track_id
    AND t.place_id = ?
    would become:
    WHERE pt.track_id = t.track_id
    AND t.place_id = '205118'
    instead of
    WHERE pt.track_id = t.track_id
    AND t.place_id = 205118

    And those get VERY different results in SQL.

  3. #3
    Join Date
    Mar 2014
    Posts
    5

    Default

    Place_Id is definitely being read as a number and has been shown to work when the second parameter is replaced by '4'.

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

    Default

    Can you provide a SQL script to create and populate a test database, and your failing workflow - stripped down to the essentials, of course?
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Mar 2014
    Posts
    5

    Default

    In trying to simplify matters I have noticed that if I set Platform_no (table PLATFORM_TRACK) to be of type varchar(3) everything works as expected, BUT defined as a CHAR(3 Byte) it does not.

    Unfortunately it is currently not feasible to change my dB to use varchar for this column - is there a work-around in Pentaho (or something I am currently not doing) in using Chars?

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

    Default

    CHAR requires padding. Use the String Operations step to pad your parameter value.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Mar 2014
    Posts
    5

    Default

    Thanks for the prompt reply

    Why should CHAR have to be padded as my equivalent SQL Developer script uses '4' for Platform_No (i.e. one character and not three as the type requires).

    Further what padding should I use?
    I am assuming: Padding->Right
    Pad Length->3
    Pad char-> not sure?

    A quick play with String operations and I still get the same result - nothing output!

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

    Default

    You don't have to pad a CHAR(3) column, that's done by the server automatically.
    When padding was necessary to store a value, it may depend on configuration settings, whether spaces on the right side are internally stripped on retrieval or not.
    I would expect your comparison succeeds when you right-pad platform_no (the field, not the column) with a space character to the defined length.
    So long, and thanks for all the fish.

  9. #9
    Join Date
    Mar 2014
    Posts
    5

    Default

    Many thanks for your help, now working happily with right-pad (white space) and pad length of three

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.