0

I have created a transformation in Pentaho Kettle (5.2 CE), and it is pulling some table data from a data source and dumping into a destination data source. In input table query I am using two named variables (para1 and para2):

enter image description here enter image description here

select col1, col2,col3 from table_ds1 where col1='${para1}' and col2='${para2}';

But it seems like the named parameters are not being substituted in the query. I am running the transformation like below:

./pan.sh -file='/dir../pull_data.ktr' -param:para1=hello -param:para2=world -Level=Basic > /dir../etl.log

My Question: Is it possible to use named variables within the same transformation? If not, how to pass named variables from outside?

andy
  • 41
  • 1
  • 8
  • 2
    Did you check "Replace variables in script" in the source step? That has to be checked or it won't do the replace. – Brian.D.Myers Mar 29 '17 at 18:29
  • @Brian.D.Myers I checked that also but it is not working. Any idea what is going wrong here? – andy Mar 30 '17 at 04:26
  • Try to pass values like this: `-param:"para1=hello" -param:"para2=world"`. Though these quotes should be required for windows only. – Andrei Luksha Mar 30 '17 at 08:17
  • Thanks it worked..but i am just wondering the way you mentioned is for Windows Machine and I am using Mac. – andy Mar 30 '17 at 09:15
  • AFAIK, windows shell treats '=' as arguments separator, whereas UNIX shells do not (unless $IFS variable was altered). Not sure about Mac, can you check your $IFS value? – Andrei Luksha Mar 30 '17 at 09:24
  • sure i will check it....how about If want to replace the same Named Parameters in Mongo Input (in query tab) like this .. {$and:[{'key1':{'$in':['${para1}']}},{'key2':{'$in':['${para2}']}}]} ...I am trying to pass the variables in the same way (as you mentioned) but seems like it is not getting resolved in Mongo Input..Any inputs? – andy Mar 30 '17 at 10:19
  • I have no experience with mongo, but your quotes look very suspicious. Are you sure, that `$in` has to be quoted? And for other DBs, we do not quote the parameter inside the IN clause, but we quote every string, which is passed inside the parameter, e.g. `WHERE col IN (${inParam})`. `-param:"inParam='a', 'b', 'c'"` – Andrei Luksha Mar 30 '17 at 13:07
  • Yes...quotes are perfect..as per mongo query syntax...I am only worried about the variable substitution. – andy Mar 30 '17 at 13:23

0 Answers0