Kettle database lookup case insensitive

Question

I've a table "City" with more than 100k records. The field "name" contains strings like "Roma", "La Valletta".

I receive a file with the city name, all in upper case as in "ROMA". I need to get the id of the record that contains "Roma" when I search for "ROMA".

In SQL, I must do something like:

select id from city where upper(name) = upper(%name%)

How can I do this in kettle?

Note: if the city is not found, I use an Insert/update field to create it, so I must avoid duplicates generated by case-sensitive names.

Answer 1

This is how I did:

First "Modified JavaScript value" step for create a query:

var queryDest="select coalesce( (select id as idcity from city where upper(name) = upper('"+replace(mycity,"'","\'\'")+"') and upper(cap) = upper('"+mycap+"') ), 0) as idcitydest";

Then I use this string as a query in a Dynamic SQL row.

After that,

IF idcitydest == 0 then 
   insert new city; 
else
   use the found record

This system make a query for file's row but it use few memory cache