1

I'm trying to substitute a comma separated string in the WHERE COLUMN IN query inside table input in PDI. I have a string query = ("car", "bike") and I want to substitute in SELECT * FROM vehicles where item in ?.

I tried to pass this string to the ? in the Table Input using replace variables and taking it from the previous step (Set Variables) which generates the query variable. It doesn't work this way because PDI adds "" around my variable and this the IN query doesn't work.

SELECT
  item_id
, GROUP_CONCAT(DISTINCT name ORDER BY item_id, name SEPARATOR '", "') AS car_names
FROM testData
GROUP BY item_id
LIMIT 1
;

Modified Java Script Value:

var str = '';

var newtax = str.concat('(', '"', car_names, '"', ')');

// next step is to Set Variable

select
  distinct name as 'car_name'
from vehicle
where name in '${query}'
union all
select "test1" as 'car_name'
from dual
union all
select "test2" as 'car_name'
from dual
union all
select "test3" as 'car_name'
from dual
limit 3
;```




expected result is 

car
bike
test1

actual result:

2019/08/27 14:30:16 - hh.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2019/08/27 14:30:16 - hh.0 - Error setting value #1 [String] on prepared statement
2019/08/27 14:30:16 - hh.0 - Parameter index out of range (1 > number of parameters, which is 0).
Puneet Sidhu
  • 95
  • 9

1 Answers1

0

In cases such as this, I would have the list in the form 'car,bike' (like what you're getting from group_concat()) and then use find_in_set() instead ofwhere ... in (...)

select
  distinct name as 'car_name'
from vehicle
where find_in_set(name, ?)
sonam_zangmo
  • 106
  • 6