topic Re: Result in where clause in Talend Studio

Result in where clause

Karuetl — Thu, 03 May 2018 18:40:49 GMT

I have a flow as below

source-->httprequest--> tExtractXMLField --> tFilterRow-->file(listofvalues)

I want the out put of the tfilterrow to be in the where clause of the query

example select * from table where col in ( list of values)

Re: Result in where clause

TRF — Thu, 03 May 2018 18:53:28 GMT

Connect a tJavaRow after the tFilterRow to construct the where clause in a global variable.
Then on the next subjob you reuse it as expected.

Re: Result in where clause

Karuetl — Thu, 03 May 2018 18:55:03 GMT

can you pls provide java code syntax

Re: Result in where clause

cterenzi — Thu, 03 May 2018 19:59:29 GMT

You can pass the list of values into a tAggregateRow with a single String column for its output. Have an empty Group By section and use the "list" function for the output column. On the Advanced Settings tab, set the Delimiter value to "','" (that's quote-singlequote-comma-singlequote-quote, i.e. " ' , ' " )

That should give you one row of output with one string value. Passed into a tJavaRow with the code:

globalMap.put("values","'" + input_row.colname + "'")

/* Add the first and last single quotes to the list and store in a global variable */

Start a new subjob with your query component. Use the global variable you just set up:
"select * from table where col in (" + ((String)globalMap.get("values")) + ")"

Re: Result in where clause

Shettydatta — Thu, 03 May 2018 20:29:41 GMT

@Karuetl

You can add a tFlowToIterate to capture each filter value and then build your Where clause in tJava

depending on the type of column build the in list in the screen shot here I have a String Column so I had to use quotes to enclose them.

I used a Global Variable to store the Where String and use it later.

Code in Java_1

String sTemp = ((String)globalMap.get("sWhere"));

sTemp += ( sTemp.length() == 0 ? "'" + ((String)globalMap.get("sTitle")) : "','" + ((String)globalMap.get("sTitle")) );

globalMap.put("sWhere", sTemp) ;

Code in Java_2

String sTemp;

sTemp = "(" + ((String)globalMap.get("sWhere")) + "')";

globalMap.putIfAbsent("sWhere", sTemp);

System.out.println(sTemp);

Re: Result in where clause

Karuetl — Thu, 03 May 2018 20:43:22 GMT

i tried as below, but output as below

Starting job

[statistics] connected
null
'2547958'
'36745585'
[statistics] disconnected

but i want them as ('2547958','36745585')

tJavaRow_1

context.inputvalues = globalMap.put("values","'" + row6.input_number + "'");
System.out.println(context.inputvalues);

In context i gave as inputvalues Object

Re: Result in where clause

Karuetl — Thu, 03 May 2018 20:46:33 GMT

i tried as below, but output as below

Starting job

[statistics] connected
null
'2547958'
'36745585'
[statistics] disconnected

but i want them as ('2547958','36745585')

tJavaRow_1

context.inputvalues = globalMap.put("values","'" + row6.input_number + "'");
System.out.println(context.inputvalues);

In context i gave as inputvalues Object

Re: Result in where clause

Shettydatta — Thu, 03 May 2018 21:06:01 GMT

Hi @Karuetl,

I wrote the following in tJavaRow and it works for me

=================

//Code generated according to input schema and output schema

sWhere is my Global Variable I am storing the data in
String sTemp;

if (StringHandling.LEN(((String)globalMap.get("sWhere"))) == 0 ) {
sTemp = "'" + input_row.Title_ID;
globalMap.put("sWhere",sTemp);
}
else {
sTemp = ((String)globalMap.get("sWhere")) + "','" + input_row.Title_ID;
globalMap.put("sWhere",sTemp);
}

output_row.where = input_row.Title_ID;

===================

Re: Result in where clause

Karuetl — Thu, 03 May 2018 21:11:59 GMT

i didnt understand this line

output_row.where = input_row.Title_ID;

should this be javarow code ?

Re: Result in where clause

Shettydatta — Thu, 03 May 2018 21:15:38 GMT

Since I use the tJava Row I had to send something out. I tried my Flow with no Output on tJavaRow and it works now as expected so you really don't need it.

This is my Code now

=============================================================

//Code generated according to input schema and output schema
String sTemp;

//output_row.where = input_row.Title_ID;

===============================================================

tJava_2 still has this closing code :

String sTemp;

sTemp = "(" + ((String)globalMap.get("sWhere")) + "')";

globalMap.put("sWhere", sTemp);

System.out.println(sTemp);

Re: Result in where clause

Karuetl — Thu, 03 May 2018 21:18:13 GMT

i dont need three output as below

need only one

[statistics] connected
null
null,'222255222'
null,'222255222','54879655'
[statistics] disconnected

Re: Result in where clause

Karuetl — Thu, 03 May 2018 21:25:20 GMT

finally got output but i want to ignore null from it

(null,'18885806040','260211251892','35351501368')

Re: Result in where clause

Shettydatta — Thu, 03 May 2018 21:28:33 GMT

Awesome Idea!! Works like charm

Re: Result in where clause

Shettydatta — Thu, 03 May 2018 21:35:42 GMT

Hi @Karuetl,

I tried the suggestion from @cterenzi and I got it to work in my tJavaRow so Still not sure what you are doing wrong why don't you output the tAggregateRow to tLogRow and see what is coming out from there. From what I tried I get the solution to work see the screenshot below.

Re: Result in where clause

Karuetl — Thu, 03 May 2018 21:36:53 GMT

help me how to get the null out

[statistics] connected
(null,'3425678','2348056','247907')
[statistics] disconnected

basically xml generated next line character and i use tfilterrow to filter only those records i need and aggregrate to generate list ..

Re: Result in where clause

Karuetl — Thu, 03 May 2018 21:39:27 GMT

i tried by checking tlogrow

here is what i got as below

[statistics] connected
2345678
4234344
[statistics] disconnected

Re: Result in where clause

Shettydatta — Thu, 03 May 2018 21:44:42 GMT

You have not set the tAggregateRow properties to list read the post above
where it says how to setup the component.

If they are set correctly like in mine you should see 1 row output in
tlogrow

Re: Result in where clause

cterenzi — Thu, 03 May 2018 21:48:03 GMT

In the tAggregateRow settings, check the box that says "Ignore null values"

Re: Result in where clause

Karuetl — Thu, 03 May 2018 21:55:19 GMT

yes i did still same

Re: Result in where clause

Shettydatta — Thu, 03 May 2018 22:18:42 GMT

Did you set output as list because that is what makes component send one
row out.

And set ignore null values