Talend community forum / [resolved] tmap : add an incremental number by other number

maxx · 2010-07-29 11:18:31

Tags: [development, incremental, java, mysql, sequence]

hello,

i work on TOS4.0.2 in java.

i try to create an incremental number who begin at 1 at each new value.
i have a mysql DB and my request is sort by col1, col2
ex :

col1 ; col2 ; col3
12 ; 678 ; RTY
12 ; 679 ; fghj
12 ; 682 ; ghj
13 ; 789 ; sd
13 ; 790 ; yu
14 ; 900 ; iu
14 ; 901 ; UYT

and i want to do this :
col0 ; col1 ; col2 ; col3
1 ; 12 ; 678 ; RTY
2 ; 12 ; 679 ; fghj
3 ; 12 ; 682 ; ghj
1 ; 13 ; 789 ; sd
2 ; 13 ; 790 ; yu
1 ; 14 ; 900 ; iu
2 ; 14 ; 901 ; UYT
...

i have tried differents solution like Numeric.sequence but it just create an incremental number without begin at 1 at each new value of col1.

thanks a lot if you can help me

shong · 2010-07-29 12:17:28

Hi
Define a custom routine to get the expected sequence number.
test1.txt:

12;678;RTY
12;679;fghj
12;682;ghj
13;789;sd
13;790;yu
14;900;iu
14;901;UYT

routine:

Code:

// template routine Java
package routines;

public class MyRoutine11784 {
    static java.util.Map map = new java.util.HashMap();
    static int currentValue = 0;
    static Integer lastValue = 0;

    public static int getSequenceNumber(int co1) {
        lastValue = (Integer) map.get(co1);

        if (lastValue == null) {
            currentValue = 1;
            map.put(co1, currentValue);
        } else {
            currentValue = lastValue + 1;
            map.put(co1, currentValue);
        }

        return currentValue;
    }
}

Result:

Code:

Starting job forum11784 at 18:09 29/07/2010.

[statistics] connecting to socket on port 3627
[statistics] connected
.---+---+---+----.
|   tLogRow_1    |
|=--+---+---+---=|
|co0|co1|co2|co3 |
|=--+---+---+---=|
|1  |12 |678|RTY |
|2  |12 |679|fghj|
|3  |12 |682|ghj |
|1  |13 |789|sd  |
|2  |13 |790|yu  |
|1  |14 |900|iu  |
|2  |14 |901|UYT |
'---+---+---+----'

[statistics] disconnected
Job forum11784 ended at 18:09 29/07/2010. [exit code=0]

Best regards
Shong

Uploaded Images

maxx · 2010-07-29 12:27:59

Thanks so much.
i have try with this http://www.talendforge.org/forum/viewtopic.php?id=6409
but it doesn t work in my configuration.

your solution work perfectly for me.

mslane · 2010-07-29 17:41:34

As you're using mySQL an alternative approach would be user defined variables in the SQL. Funnily enough, I was just writing a business objects report using these. My SQL looks similar to this.

Select if((@prev_id is null or @prev_id != Nps.i_id),@comms:= 1, @comms:=@comms+1) seq,
@prev_id := Nps.i_id,
Nps.i_id,
.....

From (select * from blah,.... Group by i_id...., order by i_id) Nps

I have no idea which method would be fastest out of this or Shong's though as it depends on your db setup etc. I'd go for the one you/your team find easiest to understand.

http://dev.mysql.com/doc/refman/5.0/en/ … ables.html

maxx · 2010-07-29 17:50:04

the solution of Shong is OK.
i ll try your and see which is better.
but i can t write or change something in the source DB.

Announcement

Unanswered posts

#1 2010-07-29 11:18:31

[resolved] tmap : add an incremental number by other number

#2 2010-07-29 12:17:28

Re: [resolved] tmap : add an incremental number by other number

Code:

Code:

#3 2010-07-29 12:27:59

Re: [resolved] tmap : add an incremental number by other number

#4 2010-07-29 17:41:34

Re: [resolved] tmap : add an incremental number by other number

#5 2010-07-29 17:50:04

Re: [resolved] tmap : add an incremental number by other number

Board footer