Test if a value that existing in a csv file field is existing in a my database field

[gadje1]

Hi everybody,

I would be able to test if a value that existing in a csv file field is existing in a my database field

Here is my structure job :

But i'm not sure how to do in my SQL query for testing if value is existing or not.

If somebody have any idea how to do that ...

Thanks in advance.

[TRF]

As tFlowToIterate create a new global variable for each field included in the input flow, change your SQL statement like this:

select col1, col2, ... from yourTable where yourField = '" + ((String)globalMap.get("ou1.yourCsvField")) + "'"

If the query returns something, the CSV field's value exists into your table, else it doesn't.

[gadje1]

Hi TRF,

thanks for your reply but i would like to catch the CODE_ORCAB that are in my CSV file but not in my DB As400.

My CSV file structure is like that :

CODE_UAB
OR00617441
100029
358406
OR02413723
100329
182
OR00617439
100741
358402
OR00617440
100742
358404
OR00595872
101455
1001
OR00595873
101488
1002
OR00595868
101490
891
OR00595874
101491
1131
OR00595870
101632
1011
OR00595875
102316
1134
OR00595869
102336
894
OR00075147
102367
668
OR00595877
102420
1135
OR00595876
102422
1132
OR00075146
102940
669
OR02413719
102941
661
OR02413720
102942
662
OR00617442
103189
358410

And my SQL query look like that :

select CDGRP from AARTICP1 where CDGRP = '"+((String)globalMap.get("out1.CODE_ORCAB")) +"'

But i didn't catch nothing.

[TRF]

What's the field name in the out1 flow schema, CODE_ORCAB or CODE_UAB?
This is important as the global variable will be named based on that field name combined with the flow name.

[gadje1]

Sorry,

i had changed the field name. it's well CODE_UAB, all is right concerning the field name now.

My query's like that :

SELECT CDGRP 
FROM AARTICP1 A INNER JOIN AARFOUP1 B on A.NOART=B.NOART
WHERE A.CDGRP  = '"+((String)globalMap.get("out1.CODE_UAB")) +"' 
OR A.ART93   = '"+((String)globalMap.get("out1.CODE_UAB")) +"'
OR B.REFFO   = '"+((String)globalMap.get("out1.CODE_UAB")) +"'

But i still not catch nothing

[gadje1]

I changed my first tMap structure output like that :

Now i catch codes that are present in both csv file & DB.

But what i want to do is catch codes that are in CSV file and not in DB.

[TRF · Accepted solution]

You should have global variables associated to the tAS400Input component, such as NB_LINE.
Connect tAS499Input to a tJava component using a If trigger with the condition ((Integer)globalMap.get("tAS400Input_1_NB_LINE")) > 0 and in tJava, System.out.println(((String)globalMap.get("out1.CODE_UAB")) + " not found")
As tJava is triggered only when no record has been found by SQL query, it will print only not found values.

[gadje1]

Hi TRf and thanks,

it seem i get what i need with "run if" connector in which i put this condition :

((Integer)globalMap.get("tAS400Input_1_NB_LINE")) == 0

Do you know how can i put it in csv file now please ?

[gadje1]

Hi TRf and thanks,

it seem i get what i need with "run if" connector in which i put this condition :

((Integer)globalMap.get("tAS400Input_1_NB_LINE")) == 0

Do you know how can i put it in csv file now please ?

[gadje1]

It's ok, i passed every global variable in a context variable and got it back it throught a tMap.