topic 'Im trying to replace IN Condition values from a context varibale in a query inside in a tOracleInput component. How to do that ? in Talend Studio

'Im trying to replace IN Condition values from a context varibale in a query inside in a tOracleInput component. How to do that ?

Anonymous — Fri, 14 Sep 2018 09:14:25 GMT

Below is my query :

select name,
(SELECT a
FROM xyz
WHERE aaa=bbb
) "Lost/Not Present/Completed "

from table1

where name in ('a','b','c')

I have ('a','b','c') in my context variable "names" . how should i replace it. i tried in the sql builder using the below query

"select ...........

------------

where name in "+context.names

Im getting SQLSyntax exception. Is there any other way .

manodwhb — Mon, 17 Sep 2018 06:42:17 GMT

@rathinTalend,can you try with below way.

select name,
(SELECT a
FROM xyz
WHERE aaa=bbb
) "Lost/Not Present/Completed "

from table1

where name in ( +context.names+)

TRF — Tue, 18 Sep 2018 13:10:05 GMT

"select name, 
(SELECT a
FROM xyz
WHERE aaa=bbb
) "Lost/Not Present/Completed "
from table1
where name in (" + context.names + ")"

Beware of " to open and clode each part before/after the variable(s).

TRF — Tue, 18 Sep 2018 15:39:00 GMT

@rathinTalend, did this help?

If so, thank's to mark your case as solved (Kudos also acceptedas a bonus).

Anonymous — Tue, 18 Sep 2018 16:35:06 GMT

There is a further change that will be required so that the column alias does not screw up the Java String.....

"select name, 
(SELECT a
FROM xyz
WHERE aaa=bbb
) \"Lost/Not Present/Completed\"
from table1
where name in (" + context.names + ")"

Since the column alias is in double quotes, you need to escape these with a \. This is because the SQL is actually a Java String.

Anonymous — Wed, 19 Sep 2018 10:52:36 GMT

Did this solution work for you?

Anonymous — Wed, 19 Sep 2018 15:33:37 GMT

Thanks rhall , i just found out the solution and forgot to post my answer over here. (Y) . Escape sequence did the trick