0

I have one sql function, let's say theFunction(item_id). It takes an item id and computes one value as its return. I read one table from the DB and I suppose to compute a new value to append for each row by this function given the item_id particular to taht row. Which desing block would do this form me with the following SQL (if not wrong).

select thsFunction(item_id);

I assume that the block gives me item_id of each row as a variable.

erogol
  • 13,156
  • 33
  • 101
  • 155
  • Just to make sure: you have values in a table which shall be passed as `item_id` into this function and the return values shall be stored in another table. Is that right? – Marcus Rickert Oct 16 '14 at 16:29

2 Answers2

1

You can use another table input step, and have it accept fields from previous steps and execute for every row (both config options are at the bottom of the step's window).

Beware that this is a rather slow implementation. Each query is executed separately and as such each row requires a round trip to the database.

Alternatively, you can use the Row SQL Script. I believe it allows you to pass all SQL statements in a single trip to the database.

nsousa
  • 4,448
  • 1
  • 10
  • 15
1

An SQL function is probably much more efficient to run in the database, for all rows at once, in stead of making a separate call into the database from PDI for each row to execute the function. So if performance is at all a relevant concern, I'd suggest a whole different strategy:

  1. Write your rows to a table in the database. End your transformation here.
  2. On the job level, first execute your transformation from above, then execute the function in an "Execute SQL script..." component, giving it an SQL command somewhat like "UPDATE my_temp_table set target_col = theFunction(item_id)".
  3. Continue your job with the remaining steps in a new transformation, starting from that table as input.

This of course presupposes that you don't have too many other threads going on, but if your transofrmation is simple and linear -- or at least if it can be made single-linear at this particular step -- it may be possible to split it up into two parts before and after this SQL call.

CRConrad
  • 11
  • 3