topic Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed? in Talend Studio

how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Wed, 03 May 2017 14:06:04 GMT

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Thu, 04 May 2017 03:52:09 GMT

Hello,

Could you please elaborate your case with an example with input and expected output values?

Best regards

Sabrina

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Thu, 04 May 2017 06:06:08 GMT

yes, my input is set of alphabets, i have to pass these alphabets to a
certain column in a table in sql db and according to these alphabets i want
filter the table and fetch the data.

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Thu, 04 May 2017 12:18:58 GMT

I think you can do something like this,

So assuming that your input contains a single column with the alphabets values, you can reference it in the tMysqlInput Component as shown above (row1.alphabet in the screenshot).

If your output is a file, make sure to select the append option so that you can consolidate the filters.

Alternatively you can always do a inner join by having the alphabets as your lookup table. This approach would be easier I think.

Hope you found this helpful.

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Thu, 04 May 2017 13:15:08 GMT

hi,
for example my a,b,c,d isthe characters i want to pass through mysqlinput
where the table contains set of names,salary,dept,etc.I want to get the
data of names starting with letters a,b,c,d. Can any1 suggest a better
answer?

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

TRF — Thu, 04 May 2017 13:24:48 GMT

You can store the "where" clause in a global variable and construct the content of this variable based on your business logic.

Then, in the tMysqlInput, concatenate the variable to the select, giving, not a sql dynamic query (from database point of view), but a query constructed dynamicaly.

Does this helps?

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Thu, 04 May 2017 13:33:23 GMT

You can use a regex in your SQL expression to filter out the data that your require.

I know it's the same design as before, but it's one of the easiest ways to do this.

You can replace the fixed flow component with an input component based on whether it comes from a file or a database.

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Fri, 05 May 2017 05:15:08 GMT

but do i need to give global variable in the sql query?

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Fri, 05 May 2017 07:06:49 GMT

Yes. That's one way to do it. Or you can use a tFilterRow or tmap after the sql component and filter the data there.

You could use something like row2.Names.matches((String)globalMap.get("row1.alphabet")+"*") as the filter condition.

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Fri, 05 May 2017 07:42:08 GMT

can i just iterate these characters to my sql input and then write a query
in the mysqlinput querybox giving a like function in where clause like
[where row1.names like "'+(string)globalMap.get(row1.alphabet)+'%"], this
should work right?

Re: how do we pass certain characters to a sql input and then fetch the data according to the the characters passed?

Anonymous — Fri, 05 May 2017 07:51:10 GMT

yup it should. You can just try giving row1.alphabet. If that doesn't work, then you can explicitly get the value by using the get method,

(string)globalMap.get("row1.alphabet").

Also your syntax seems a bit off.

It should be where row1.names like '"+(string)globalMap.get("row1.alphabet")+"%'" i.e make sure your double quotes and single quotes are properly enclosed.