Six Stars

Result in where clause

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)

 

20 REPLIES
Thirteen Stars TRF
Thirteen Stars

Re: Result in where clause

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.

TRF
Six Stars

Re: Result in where clause

can you pls provide java code syntax 

Ten Stars

Re: Result in where clause

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

Six Stars

Re: Result in where clause

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

 

 

Talend.jpg

Six Stars

Re: Result in where clause

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 

1.PNG

 

 

Six Stars

Re: Result in where clause

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 

1.PNG

 

 

Six Stars

Re: Result in where clause

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;

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

Talend_1.jpg

 

Six Stars

Re: Result in where clause

i didnt understand this line 

output_row.where = input_row.Title_ID; 

 

should this be javarow code ?

Six Stars

Re: Result in where clause

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);Talend.jpg

System.out.println(sTemp);

 

Six Stars

Re: Result in where clause

i dont need three output as below 

need only one 

 

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

 

 

Six Stars

Re: Result in where clause

finally got output but i want to ignore null from it 

 

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

Six Stars

Re: Result in where clause

Awesome Idea!! Works like charm
Six Stars

Re: Result in where clause

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. 

 

Talend.jpg

Six Stars

Re: Result in where clause

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

 

1.PNG

Six Stars

Re: Result in where clause

i tried by checking tlogrow 

here is what i got as below 

 

[statistics] connected
2345678
4234344
[statistics] disconnected

Six Stars

Re: Result in where clause

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
Ten Stars

Re: Result in where clause

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

Re: Result in where clause

yes i did still same 

Capture.PNG

 

Six Stars

Re: Result in where clause

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

And set ignore null values
Nine Stars

Re: Result in where clause

HI,

 

Please try the following process. You have two records from XML extract and using tAggregate list function concatenate two values in single record. the using tJavarow assign that value to context variable. tJava used to display context value. tOracleInput used context value and return output.

ListValues3.PNGListValues4.PNGListValues5.PNGListValues6.PNGListValues7.PNG

Regards,

Veeru Boppudi