Post a reply

Write your message and submit

Options

Click in the dark area of the image to send your post.

Go back

Topic review (newest first)

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.

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

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

maxx
2010-07-29 11:18:31

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 wink

Board footer

Powered by FluxBB