write sql query which needs to compare values which are in a csv file

[android1686764069]

Hello

I want to write a sql query in a tOracleRow component.

The query is someting like:

UPDATE MY_TABLE

SET MY_TABLE.visited ="YES"

WHERE MY_TABLE.id IN ("100" , "200")

you see the last image for an example of my table

Instead of harcoding the values 100,200, i want to get them from a csv file (file3.csv) which looks like this: (see pic file3.jpg)

I have a job which is like this :

(see pic job.jpg)

How can I can replace ("100" , "200"), by the value in the first column of my csv file?

[anselmopeixoto]

Hi @android devops​ 

Try using tDenormalize component. With it you can pivot the id column from the csv file to a single String containing the list of values from that column.

[android1686764069]

I watched a video on YouTube to understand tdenormalize. I think I got it. However when the data is denormalized, how can I put it in the SQL query? I'm thinking of a context but how can I store tte denormalized data in a context?

I'm a beginner

[anselmopeixoto]

Hi @android devops​ 

You can use String concatenation. Let's say you have tDenormalize connected to tDBRow by a row called "row3", then you can use the following on your tDBRow component:

"UPDATE MY_TABLE SET MY_TABLE.visited = \"YES\" WHERE MY_TABLE.id IN ("+row3.id+")"

Please notice that since the value "YES" is between quotes, you'll need to escape those characters using a backslash. Now if you use a single quote character ' that won't be necessary.

Another important thing is that since the IDs you provided in the example are Strings (aaa, bbb...) you'll need to encapsulate them in quotes or single quotes before denormalizing the column. You can use a tMap or tJavaRow to concatenate a single quote to id column like this:

"'" + row1.id + "'"

Finally, tDenormalize must get only the id column as input so it can create the list correctly on the output column.

Simply put, your Job would look like this:

tFileInputDelimited -row1-> tJavaRow (or tMap) -row2-> tDenormalize -row3-> tDBRow

[android1686764069]

thanks

I was thinking of something: could I just use a csv file wille the data iw ant to update with tdoutput? No need to dnormalize and all the other stuffs

[anselmopeixoto]

You could do this way, but that would create one update statement for each id in your source file.

To fill the "in" clause on the SQL command, the ids would need to be already in a comma separated list, or you will need to pivot the rows to create the list during job execution.

[android1686764069]

Not sure i understand what you said. If I have 5 items in my IN clause, i would have 5 lines.

something like that