Hitachi Vantara Pentaho Community Forums
Results 1 to 15 of 15

Thread: Passing a variable parameter to the sql query

  1. #1
    Join Date
    Jan 2008
    Posts
    6

    Default Passing a variable parameter to the sql query

    Hello,
    I'm trying to make a transformation where I extract the value of the date_entered for the most recent account from a mysql table,
    then pass the value of this field over to the Oracle and trying to use as variable parameter in the where clause for the Oracle Select Query.
    So Far I only have use 2 steps: both are table input. The queries are correct I tested them in both systems, but it does not seem like my paramter is being passed. Any help would be appreciated.
    Thanks
    Alex

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

    Default

    It depends on what you actually mean... first read this http://wiki.pentaho.org/display/EAI/...Input+Step+FAQ and then come back to us

    Regards,
    Sven

  3. #3
    Join Date
    Jan 2008
    Posts
    6

    Default

    Hello Sven,
    Thanks for getting back this is what I'm trying to do.
    I'm running a query to MySQL database :

    select date_entered from accounts order by date_entered desc limit 1;

    which actually returns only one row with a value of the date_entered field in it ( It is a Table Input Step).
    This is the value of this field returned I can see it in the preview mode for this step: 2008-01-10 17:54:53

    I'm creating another step to the Oracle DB
    Table and run this where one of the where clause parameters I substitute with ?
    SELECT

    substr(to_char(ACM.ACCOUNT_ID),1,35) id,

    TO_CHAR(ACM.CREATION_DATE,'YYYY-MM-DD HH24:MI:SS') date_entered,

    sysdate date_modified,

    'Modified By' modified_user_id,

    'Assigned To' assigned_user_id,

    'Rene Rodriguez' created_by,

    substr(to_char(TRI.FIRST_NAME||' '||TRI.LAST_NAME),1,149) name,

    'X' parent_id,

    substr(to_char(FXT.TRADESET_NAME),1,24) account_type,

    substr(to_char(TC.CTIER_NAME),1,24) industry,

    substr(to_char(SUM(ACA.BALANCE)),1,24) annual_revenue,

    'No Phone fax' phone_fax,

    substr(to_char(TRI.ADDRESS),1,149) billing_address_street,

    substr(to_char(TRI.CITY),1,99) billing_address_city,

    substr(to_char(TRI.PROVINCE),1,99) billing_address_state,

    substr(to_char(TRI.ZIP),1,19) billing_address_postalcode,

    substr(to_char(BC.FULL_NAME),1,99) billing_address_country,

    'Description' description,

    'No comment' rating,

    substr(to_char(TRI.HOME_PHONE),1,24) phone_office,

    '000-000-0000' phone_alternate,

    substr(to_char(TRI.EMAIL),1,99) email1,

    '' email2,

    substr(to_char(ACM.ACCOUNT_ID),1,254) website,

    substr(to_char(BSE.FULL_NAME),1,99) ownership,

    '' employees,

    substr(to_char(TRM.IB_ID),1,9) sic_code,

    substr(to_char(MIN(AU.LOGIN_NAME)),1,9) ticker_symbol,

    '' shipping_address_street,

    '' shipping_address_city,

    '' shipping_address_state,

    '' shipping_address_postalcode,

    '' shipping_address_country,

    '0' deleted,

    '0' campaign_id

    FROM TRM_RT_INFO TRI,

    BSE_COUNTRY BC,

    TRM_RETAIL_TRADER TRM,

    ACM_ACCOUNT ACM,

    ACM_CURRENCY_ACCOUNT ACA,

    TRM_MT_IN_MM TM,

    TRM_CLIENT_TIER TC,

    FXD_TRADESET FXT,

    BSE_BUSINESS_PARTY BSE,

    ASM_BPARTY_USER ABU,

    ASM_USER AU

    WHERE

    TRM.RT_ID=TRI.RT_ID

    AND TRI.COUNTRY_ID=BC.COUNTRY_ID(+)

    AND TRM.RT_ID=ACM.OWNER_ID

    AND ACM.ACCOUNT_ID=ACA.ACCOUNT_ID(+)

    AND ACM.CREATION_DATE > to_date(substr('?',1,19),'YYYY-MM-DD HH24:MI:SS')

    AND TM.MT_ID(+)=TRM.RT_ID

    AND TM.CTIER_ID=TC.CTIER_ID(+)

    AND TM.TRADESET_ID=FXT.TRADESET_ID(+)

    AND TRM.IB_ID=BSE.BPARTY_ID

    AND TRM.RT_ID=ABU.BPARTY_ID(+)

    AND ABU.USER_ID=AU.USER_ID(+)

    GROUP BY

    ACM.ACCOUNT_ID,

    ACM.CREATION_DATE,

    TRI.FIRST_NAME,

    TRI.LAST_NAME,

    TRI.ADDRESS,

    TRI.CITY,

    TRI.PROVINCE,

    TRI.ZIP,

    BC.FULL_NAME,

    TRI.HOME_PHONE,

    TRI.EMAIL,

    TRM.IB_ID,

    BSE.FULL_NAME,

    TC.CTIER_NAME,

    FXT.TRADESET_NAME

    ORDER BY ACM.ACCOUNT_ID


    This is the actual line where the substituition should take place:
    AND ACM.CREATION_DATE > to_date(substr('?',1,19),'YYYY-MM-DD HH24:MI:SS')
    Well nothing happens... Am I doing anything wrong? This query works if I just pass the parameter generated by the first query, if executing my query directly. In the second step I check Replace variables and select first step in the Insert data from step as my input step.
    Thanks
    Alex
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2008
    Posts
    6

    Default

    I spent a whole day trying to accomplish a seemingly simple thing - Extract a field value from a Mysql table and pass it over as a parameter to Oracle database(as a part of the where clause as ?), nothing seemed to work... Is It doable at all? At this point it seems like it is easier for me to write a php script, put all the data manipulation logic in script, and then simply add an entry to the cron table so it is run every day...
    I'm trying work on a project and my boss wants to use Pentaho Kettle for data transfer automation attached is a description of what I'm actually trying to accomplish. Do you think that Pentaho has means to let me do it? Any advice would appreciated.
    Thanks
    Alex
    Attached Files Attached Files

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

    Default

    Don't use 's around the ? ... AND insert rows from hop (select your hop) and execute for each input row switched on.

    For the rest I don't really see anything which can't be done.

    Regards,
    Sven
    Last edited by sboden; 01-15-2008 at 06:38 PM.

  6. #6
    Join Date
    Jan 2008
    Posts
    6

    Default

    i'M still stuck with the same issue, it does not let me pass a variable, I tried to use an intermediary step - modified javascript and it works ok if you simply pipe date_entered parameter into text file, but once it comes to Oracle, it does not want to execute my query and returns an error, since there is no substitution happening... since Oracle has no idea what a question mark stands for...
    Thanks
    Alex

  7. #7
    Join Date
    Jan 2008
    Posts
    6

    Default

    I even tried to simplify situation,
    in the first step I run
    SELECT billing_address_street FROM accounts order by date_entered desc limit 1 for the Mysql table input step
    then I'm adding a hop and connecting it to the oracle db table input and use the following query:
    select ADDRESS from TRM_RT_INFO where ADDRESS = ?;
    but I get an error in kettle log file when I'm running this transformation:
    2008/01/16 13:45:33 - Oracle.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : select ADDRESS from TRM_RT_INFO where ADDRESS = ?;
    2008/01/16 13:45:33 - Oracle.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ORA-00911: invalid character
    Which seems to me like this billing_address street paramter is not being passed and does not conver question mark into a proper value,that's why it returns an error message, any ideas?
    Thanks
    Alex

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

    Default

    Loose the ; at the end.

  9. #9
    Join Date
    Jan 2008
    Posts
    3

    Default success in passing parameters to input step

    I have succeeded with Oracle XE passing a parm (date) from a previous input step. If the suggestions above do not work, let me know and I will explain further. All this just to say it does work.

  10. #10
    Join Date
    Sep 2006
    Posts
    8

    Default

    Hi would you mind sharing your solution, it seems to be a common problem.


    Thanks,

  11. #11

    Default

    Hello,
    I have the same problem.
    It would be great if someone could post his solution.

    Thank you in advance,
    Lars

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

    Default

    Which exact problem do you have... there are several in this thread.

    2 ways to get "variable things" in table input:
    - You either let rows arrive at the table input, which contain the parameters for the query in a positional way. Activate "execute for row" and specify "insert data from hop", and use ?'s in the SQL. This uses the database variable binding and there are some restrictions on it: you can't bind tablenames e.g.
    - Use variables in the format ${VARIABLE}: you define the variables upfront and you can just use them in the SQL if you activate "replace variables in script". Of course if you want multiple values for 1 variable in a run, you have to make a kind of loop with jobs and transformations (setting variable, execute table input, setting variable, ...)

    Regards,
    Sven

  13. #13

    Default

    Thank you for your summing up Sven,
    now it works!

    Lars

  14. #14
    Join Date
    Mar 2009
    Posts
    1

    Default Select the "Insert data from step" as "Sugar ..."

    adambrouski.

    Select in the "oracle step", "Insert data from step" as "Sugar ...".
    Then, it will work.

  15. #15
    Join Date
    Oct 2013
    Posts
    6

    Default

    Quote Originally Posted by r_gannu View Post
    adambrouski.

    Select in the "oracle step", "Insert data from step" as "Sugar ...".
    Then, it will work.
    Thank you for your solution. I`ve tried to insert value to db with sql execution transformation but it fails without any reason, but this solution works.

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.