talend

[rohit1804]

Hi There i have coded following code in tJDBC input.

"select count(1) as record_count , '"+context.primary_keys+"' as primary_keys from
"+context.table_name+"

group by '"+context.primary_keys+"'
having count(1) =1
"

I am not able to find the result of above query which is showing error at group by clause.

Can anybody help to know how talend read context variable in group by clause??

[fdenis]

"select count(1) as record_count , '"+context.primary_keys+"' as primary_keys from
"+context.table_name+"

group by '"+context.primary_keys+"'
having count(1) =1
"

count(1) !  count(fieadName), count(*) or sum(1)

context.primary_keys have to be not empty it's comma separated list of fields name.

fields name: are with non spaces or quoted depending on your db.

if primary_keys are primary keys, all results are going to be unique! no need to count!

[rohit1804]

Hi fdenis,

Actually what i am doing know , i want duplicates record which is present in my table so i read the table from my DB  and i have updated primary keys in primary_keys columns  and then i used tjava component to read my table name and primary_keys so i created context variable.i used that context variable in tjdbc component and coded as   ,

"select count(*) as record_count , '"+context.primary_keys+"' as primary_keys from
"+context.table_name+"

group by '"+context.primary_keys+"'
having count(*) >1
"

But i am not getting my expected result .Please find the screenshot that i have uploaded.

job.PNG

[fdenis]

use tJavaRow to set data row4.primary_key and row4.table_name during the main part of the job. in tMAp set "reload for each row" set globalMap Values here
your query must be something like that:
"select count(*) as record_count , '"+globalMap.get("primary_keys")+"' as primary_keys from
"+globalMap.get("table_name)+"
group by '"+globalMap.get("primary_keys")+"'
having count(*) >1
"
(queries are build in the begin of the job)

3 levels of variables
context, globalMap, var

[rohit1804]

Thanks fdenis,

need one more help

As i am new to talend how and where  should i defined globalmap variable?

[fdenis · Accepted solution]

in tFlowToIterate,in tSetGlobalVar and indirectly somme other components like tFileList….

[rohit1804]

Thanks 

My job is executed successfully .