How to merge multiple rows?

[Anonymous]

I have a question about merge multiple rows from DB query. For example, query results:
id col1 col2 col3 col4
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
Merge condition is:
row1.col1= row2.col1
and row1.col2=row2.col2
and row1.col3 like 'AB%'
and row2.col3 like 'AB%'
Then let row2.col4 = row2.col4+row1.col4. So after merge, output should be:
id col1 col2 col3 col4
row2 key1 key2 ABC 13
How can i make it? Appreciate your help!

[Anonymous]

Hi
I need to make sure one thing. All these rows are from one table or two table?
If they are in the same table, how many rows in it?
Regards,
Pedro

[Anonymous]

Hi
I need to make sure one thing. All these rows are from one table or two table?
If they are in the same table, how many rows in it?
Regards,
Pedro

Hi, Pedro
They are from one store procedure result.

[Anonymous]

Hi
What about three columns?
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
row3 key1 key3 ABC 3
What's the rule for row3?
Regards,
Pedro

[Anonymous]

Hi
What about three columns?
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
row3 key1 key3 ABC 3
What's the rule for row3?
Regards,
Pedro

Just output row3 directly, because it couldn't be merged on either row1 or row2. Merge conditon is
row1.col1= row2.col1
and row1.col2=row2.col2
and row1.col3 like 'AB%'
and row2.col3 like 'AB%'
if row3.col2 equals key2 not key3, it will be merged! Thanks for your quick reponse.

[Anonymous]

Hi
You might create a job as the following images.
Code in tJava

//Code generated according to input schema and output schema
Pattern pt = Pattern.compile("^AB*.");
String sm1 = col3_tMemorizeRows_1==null?"":col3_tMemorizeRows_1;
String sm0 = col3_tMemorizeRows_1; 
String col1_1= col1_tMemorizeRows_1==null?"":col1_tMemorizeRows_1;
String col2_1= col2_tMemorizeRows_1==null?"":col2_tMemorizeRows_1;
Matcher m1=pt.matcher(sm1);
Matcher m0=pt.matcher(sm0);
if((col1_1.equals(col1_tMemorizeRows_1))&&
(col2_1.equals(col2_tMemorizeRows_1))&&m1.find()&&m0.find()){
   output_row.id = id_tMemorizeRows_1;
   output_row.col1 = col1_tMemorizeRows_1;
   output_row.col2 = col2_tMemorizeRows_1;
   output_row.col3 = col3_tMemorizeRows_1;
   output_row.col4 = col4_tMemorizeRows_1+col4_tMemorizeRows_1;
}else{
output_row.id = input_row.id;
output_row.col1 = input_row.col1;
output_row.col2 = input_row.col2;
output_row.col3 = input_row.col3;
output_row.col4 = input_row.col4;
}

Regards,
Pedro

[Anonymous]

Hi
You might create a job as the following images.
Code in tJava

//Code generated according to input schema and output schema
Pattern pt = Pattern.compile("^AB*.");
String sm1 = col3_tMemorizeRows_1==null?"":col3_tMemorizeRows_1;
String sm0 = col3_tMemorizeRows_1; 
Matcher m1=pt.matcher(sm1);
Matcher m0=pt.matcher(sm0);
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find()){
   output_row.id = id_tMemorizeRows_1;
   output_row.col1 = col1_tMemorizeRows_1;
   output_row.col2 = col2_tMemorizeRows_1;
   output_row.col3 = col3_tMemorizeRows_1;
   output_row.col4 = col4_tMemorizeRows_1+col4_tMemorizeRows_1;
}else{
output_row.id = input_row.id;
output_row.col1 = input_row.col1;
output_row.col2 = input_row.col2;
output_row.col3 = input_row.col3;
output_row.col4 = input_row.col4;
}

Regards,
Pedro

Hi Pedro,
i am still confused how did you generate the code? where is your merge conditon definition? Thanks a lot.

[Anonymous]

Hi
The merge condition definition is decided by 'if' statement.
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find())
In fact, your requirement is complicated. It took me time to write these code.
Regards,
Pedro

[Anonymous]

Hi
The merge condition definition is decided by 'if' statement.
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find())
In fact, your requirement is complicated. It took me time to write these code.
Regards,
Pedro

Thanks again, Pedro. But looks like records are not merged.
input:
row1;key1;key2;AB;5
row2;key1;key2;ABC;8
row3;key1;key3;ABC;3
output should be:
row2;key1;key2;ABC;13
row3;key1;key3;ABC;3

[Anonymous]

Hi
I have corrected my mistake.
You will see the right solution at Comment #6 this time.
Besides, you have to remove the first line manually.
Regards,
Pedro