Result in where clause

[Karuetl]

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)

[TRF]

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.

[Karuetl]

can you pls provide java code syntax 

[cterenzi]

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")) + ")"

[Shettydatta]

@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);

[Karuetl]

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 

[Karuetl]

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 

[Shettydatta]

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;

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

[Karuetl]

i didnt understand this line 

output_row.where = input_row.Title_ID; 

should this be javarow code ?

[Shettydatta]

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;

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;

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

tJava_2 still has this closing code :

String sTemp;

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

globalMap.put("sWhere", sTemp);

System.out.println(sTemp);