Talend community forum / using context variables in global sql queries

Marco Westermann · 2012-06-08 12:02:29

Hi,

I have a set of sql-queries defined globally in my project. Many of them have an in clause like the following to filter the result set:

and p_nid in (5,6,8,9,11,13,14,15,66)

I would like to define the ids as context variable and refer them in my sql-query. Is that somehow possible?

thank you for suggestions,

regards,

Marco

pedro · 2012-06-08 12:06:55

Hi Marco

The query is like this.
"and p_nid in ("+context.id1+","+"context.id2+","+context.id3+")"

Regards,
Pedro

Marco Westermann · 2012-06-08 15:37:19

Hi,

thank you very much, works like a charm.

regards, Marco

jpetsche · 2012-06-08 18:54:12

Could I do a similar thing where I use a context variable inside a context variable?

For example if I store just the where clause in a context variable:
context.where_sql = "in ("+context.id1+","+context.id2+","+context.id3+")"

pedro · 2012-06-11 03:43:53

Hi

Sorry. Talend can't support saving context variable into context variable.

Regards,
Pedro

pgmuthukumar · 2012-06-11 06:08:07

can the context hold list of values?
so that way we need not to hard code/restrict the number of variables like id1, id2, ... idn

pedro · 2012-06-11 07:02:42

Hi

The answer is no.

Regards,
Pedro

pgmuthukumar · 2012-06-11 07:09:05

ok, in that case is there any alternate way to implement?

jpetsche · 2012-06-11 17:33:46

The only way I know is via a tFlieDelimited. The file would contain a list of variables to use:

id1
id2
id3
...

Talend would iterate through that list. Perhaps you can call those variables another way??

avdbrink · 2012-06-11 21:57:56

Hi,

Why not use a context variable named "ids" like this:

context.ids = "5,6,8,9";

And then in your query use:

"where id in (" + context.ids + ")"

I see no reason this could not work. Can you explain why you said no Pedro?

Regards,
Arno

pedro · 2012-06-12 03:38:17

Hi Arno

I said no because I think what pgmuthukumar means is Array or List data pattern which is able to add element and remove element, not String.

Regards,
Pedro

avdbrink · 2012-06-12 07:38:18

Hi Pedro,

Okay, I get that, that's a No indeed

Regards,
Arno

Announcement

Unanswered posts

#1 2012-06-08 12:02:29

using context variables in global sql queries

#2 2012-06-08 12:06:55

Re: using context variables in global sql queries

#3 2012-06-08 15:37:19

Re: using context variables in global sql queries

#4 2012-06-08 18:54:12

Re: using context variables in global sql queries

#5 2012-06-11 03:43:53

Re: using context variables in global sql queries

#6 2012-06-11 06:08:07

Re: using context variables in global sql queries

#7 2012-06-11 07:02:42

Re: using context variables in global sql queries

#8 2012-06-11 07:09:05

Re: using context variables in global sql queries

#9 2012-06-11 17:33:46

Re: using context variables in global sql queries

#10 2012-06-11 21:57:56

Re: using context variables in global sql queries

#11 2012-06-12 03:38:17

Re: using context variables in global sql queries

#12 2012-06-12 07:38:18

Re: using context variables in global sql queries

Board footer