CODEWITHSUNDEEP
pentaho
tester
tester

Reputation: 223

pentaho database join error to match input data

I have an input.csv file in which I have field "id" . I need to do a database lookup with below logic.

I need to search whether the "id" is present in the field "supp_text"and extract the field "loc_id".

Eg, id = 12345. and, in my supp_text, I have the value "the value present is 12345".

I am using "Database join" function to do this. viz.

 *select loc_id from SGTABLE where supp_text like '%?%';*

 and, i am passing "id" as a parameter.

I get the below error when I run.

"Couldn't get field info from [select LOC_ID from SGTABLE WHERE SUPP_TEXT like '%?%']" offending row : [ID String(5)]

all inputs are string, and table fields are "VARCHAR". .

I tried with "database lookup option too. But it does not have an option to match substring within a string.

Please help.

Upvotes: 0

Views: 2616

Answers (1)

Brian.D.Myers
Brian.D.Myers

Reputation: 2518

The JDBC driver is not replacing the parameter within the string. You must make the wildcard string first and pass the whole thing as a parameter. Here is a quick transform I threw together that does just that:

enter image description here

Note that in the Database Join step the SQL does not have '' quotes around it. Note also that unless used properly, the Database Join step can be a performance killer. This however, looks to be a reasonable use of it if there are going to be a lot of different wildcard values to use (unlike in my transform).

Upvotes: 1

Related Questions