why the execute sql script is not working in kettle

2
votes

I am using kettle to get data from one table (t1) and joining execute sql script tool (t2) and then making an insert/update in the same table (t1)

Here's my transform

table input tool

select stud_id,mark from student;

execute sql Script

select s.stud_id,ifnull(m.mark,0) as mark from mark as m inner join student as s on (s.stud_id=m.stud_id) where s.student_id='?'

fields: stud_id

insert/update tool

  • table: student
  • check: stud_id=stud_id
  • update: mark=mark

When I run this, it's successfully running but the values are not inserted or updated in target table.

1
kettle

1 Answers

2
votes

If the Execute SQL Script step is the one I think your referring to, it doesn't generate output. It's for forming up a bunch of SQL statements in your transform and running them individually. I'm not in front of PDI right now, but I believe the way to run a dynamic SQL statement and add its output to your data flow is the Execute Dynamic SQL step.

Never the less, in your case I would use a Database Join step instead. This step prepares the statement and just re-executes the query plan for each row that arrives in the transform, substituting data into parameter markers. Much more performance friendly.