Four Stars

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

 
10 REPLIES
Moderator

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

Hello,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

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

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.
Seven Stars

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

I think you can do something like this,

filterSQL.PNG

 

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.

Four Stars

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

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?
Twelve Stars TRF
Twelve Stars

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

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?


TRF
Seven Stars

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

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.

extractSQL.PNGtMySQLcomponent.PNGtFixedFlowComponent.PNG

 

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

 

 

Four Stars

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

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

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

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.

Four Stars

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

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?
Seven Stars

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

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.