Six Stars

Auto increment an ID if other ID exists

Dear experts, 

 

I would like to do an auto increment on a field in function of what I receive. 

If my couple : car_id & sequence_id already exists. I would like to increment sequence_id

 

For ex : I would like to have : 

 

car_id        sequence_id (by auto increment)

1                 1 

2                 1

2                 2

3                 1

4                 1

4                 2

4                 3

4                 4

 

Hope I am clear.

Thank you for your help. Best regards. 

Romain

 

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars TRF
Twelve Stars

Re: Auto increment an ID if other ID exists

Don't know what you want to do with that SQL update.

Read this for an explaination of how to work with lookup in tMap (search for "Read at each Row" in the page, this is what you need if I understand your case).


TRF
11 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Auto increment an ID if other ID exists

Hi,

Here it is:

Capture.PNG

Fields are all declared as Integer.

 

Hope this helps.


TRF
Six Stars

Re: Auto increment an ID if other ID exists

Dear TRF,

 

Thank you for your answer. 

 

I do the same formula as you. It works fine I have : 

car_id                sequence_id

1                           1

2                           1

3                           1

                          1

 

But, for the 2nd run, when I want to insert (not update) in my database, I have the error message that I have a duplicate keys (so the increment doesn't work in this case). 

Please find copies of my error

 

Thank you for your help. 

Regards

Twelve Stars TRF
Twelve Stars

Re: Auto increment an ID if other ID exists

Hi,

This case wasn't explain in your 1rst post.

3 possibilities:

- store locally (on a file for example) the current sequence value for each car_id and reuse it as a sequence start value for the next run

- get the max sequence_id for each car_id when the job starts and reuse it as a sequence start value

- get the max sequence_id for the current car_id using a tMap with lookup to query the db for each input row

Does this helps?


TRF
Six Stars

Re: Auto increment an ID if other ID exists

Hi, 

 

I'm sorry if I wasn't clear. 

I think the 3rd solution is the best in my case. 

 

Hum, correct me if I'm wrong  

In tPostgresqlRow, 

 

select max(sequence_id) from car_temp where source_car_id = database_car_id 

 

case max(sequence_id)

        when not null then max(sequence_id) + 1

        else max(sequence_id)

end

 

Regards

Twelve Stars TRF
Twelve Stars

Re: Auto increment an ID if other ID exists

select max(sequence_id) where source_car_id = database_car_id 
case max(sequence_id)
        when not null then max(sequence_id) + 1
        else 1
end

change the else clause.


TRF
Six Stars

Re: Auto increment an ID if other ID exists

Hi,

 

My clause doesn't work : 

 

UPDATE car_temp SET sequence_id = 
(case (select max(car_staging.sequence) from car_staging,car_temp 
where car_temp.car_id = car_staging.car_id)
       when not null then 
                (select max(car_staging.sequence) from car_staging , car_temp where 
                 car_temp.car_id = car_staging.car_id + 1)
        else (1)
end)

And I have this error : 

 

ERROR: operator does not exist: integer = boolean
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Twelve Stars TRF
Twelve Stars

Re: Auto increment an ID if other ID exists

Don't know what you want to do with that SQL update.

Read this for an explaination of how to work with lookup in tMap (search for "Read at each Row" in the page, this is what you need if I understand your case).


TRF
Six Stars

Re: Auto increment an ID if other ID exists

Ok, I try to do your 3rd method but by sql query. 

 

Hum so, forget my previous message, can you detail your 2nd and 3rd method please (which component ...)

 

How can I get the max(sequence_id) in my source db ? 

 

Please find my job

Twelve Stars TRF
Twelve Stars

Re: Auto increment an ID if other ID exists

Refer to the PostgreSQL document but I think you need to select nextval to be able of select currval (that's not what you want).

So you need to select the max value from the table, not the sequence.

All details for solution 3 are included in the link I gave you in the previous post - havec you read id yet?

The better way is to try by yourself.


TRF
Six Stars

Re: Auto increment an ID if other ID exists

Hi, 

 

I've succeeded with your link Smiley Happy 

Thank you. 

 

Best regards.

Twelve Stars TRF
Twelve Stars

Re: Auto increment an ID if other ID exists

Great!

TRF