Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Transformation Arguments

  1. #1

    Default Transformation Arguments

    Attachment: Sans titre.JPG Hello,


    I have one transformation TRANS A with
    a Table Input
    SELECT
    case count(*)
    WHEN 0 THEN 2
    ELSE count(*)
    END AS nr
    FROM T_Trans_Log
    WHERE
    ID_BATCH=(SELECT MAX(ID_BATCH)
    FROM T_Trans_Log
    WHERE TRANSNAME='?')
    AND TRANSNAME='?';



    ? is the name of one transformation TRANS B passed in arguments



    I have tried to pass this argument (name of transformation) in Fields Arguments ("Transformation Step" in Chef)..Please see attached image..but i can success.



    The idea is to pass a transformation Name to TRANS B which retour the result of the previous query for this transformation (sorry..a lot of transformation in my words






    An idea ?



    Thanks
    Samatar

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

    Default RE: Transformation Arguments

    The arguments you specify can be obtained with the "Get System Info Step".
    These arguments can then be passed to a Table Input step.
    You need to loose the quotes (&#39 around the ? though.

    All the best,

    Matt

  3. #3

    Default RE: Transformation Arguments

    Hi matt,

    Ok..But which information in "Get Systme Info Step" do i use?


    Thanks
    Samatar

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

    Default RE: Transformation Arguments

    Euuh, how about "Command line argument 1" etc?

    ;-)

    Matt

  5. #5

    Default RE: Transformation Arguments

    Good..It works..Thanks a lot

    I can now check if the trans is still working before running it...
    Samatar

  6. #6

    Default didnt work for me :(

    Hi
    In my transformation after one insert/update I am getting time_id [with all other fields]. To remove all the extra fields I added one "select value" and only getting the time_id. After that I used "unique rows" to get unique time_id.
    Now I tried to get data from a table, so I used "table input" after "unique rows". So I should get only time_id as the argument to my "table input". I selected that step as the "insert data from step", but its not working, the hops is getting yellow.

    I searched forum and found that "table input" only takes arguments from "get system info". So I added it after my "unique rows", and in "get system info" I selected "command line argument 1". But still the "table input" not taking the argument and the hops is still yellow [i changed that "insert data from step" to "get system info".

    When I try to select the "show input fields" on "table output" it says "I couldn't find any fields".
    Please help.

    Thanks in advance

  7. #7
    DEinspanjer Guest

    Default

    There is nothing about Table Input only being able to read from Get System Information.
    You were close to the right solution with your first try. There was probably just something simple left out.
    Any chance you could attach the transformation or at least screen shot the table input dialog?

  8. #8

    Default

    Hi
    Here is the transformation file attached for your reference.
    Last edited by dyutiman; 06-09-2008 at 06:26 AM.

  9. #9

    Default

    And here is the snippet of the error I am getting :

    2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException:
    2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)]
    2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
    2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [time_id_1_1_1 Integer(10)] on prepared statement (Integer)
    2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
    2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Parameter index out of range (2 > number of parameters, which is 1).
    2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ERROR in part: P Set values
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Unexpected error :
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : org.pentaho.di.core.exception.KettleDatabaseException:
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : An error occurred executing SQL in part [P Set values]:
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : select
    datetime .start_day,

    time_id as id_of_time,

    account_id as id_of_account ,

    sum(Clicks) as 'Total Clicks',

    sum(Imps) as 'Total Impressions',

    sum(sales_count) as 'Purchases',

    sum(lead_count) as 'Leads',

    sum(signup_count) as 'Signups',

    round(sum(Cost)/1000000,2) as 'Total Cost',

    sum(Conv) as 'Total Conversions',

    round(sum(Clicks)*100/sum(Imps),2) as 'Click Through Rate',

    round(sum(Cost)/(sum(Clicks)*1000000),2) as 'Avg CPC',

    round(sum(Conv)*100/sum(Clicks),2) as 'Conversion Rate',

    round(sum(Cost)/(sum(Conv)*1000000),2) as 'Cost Per Conversion',

    round(avg(pos),2) as 'Average Position'

    from fact,datetime where fact.time_id=datetime.id and datetime.id = ?

    GROUP BY account_id,time_id;
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)]
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [time_id_1_1_1 Integer(10)] on prepared statement (Integer)
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
    2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Parameter index out of range (2 > number of parameters, which is 1).

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

    Default

    Don't hi-jack threads ... in your case the problem is easy "2 > number of parameters, which is 1" ... you have more than 1 field in the row coming into table input.

    Use the "insert data from step" and switch on "execute for each row".

    Regards,
    Sven
    Last edited by sboden; 05-08-2008 at 06:35 AM.

  11. #11

    Default

    thanks
    That really worked. Actually I got confused about that yellow hops and when I right clicked the table input and tried to view the input data, it was showing nothing.
    Anyway, now its working.

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.