topic Re: Auto increment an ID if other ID exists in Talend Studio

Auto increment an ID if other ID exists

Anonymous — Tue, 14 Nov 2017 17:07:15 GMT

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

Re: Auto increment an ID if other ID exists

TRF — Tue, 14 Nov 2017 17:14:56 GMT

Hi,

Here it is:

Fields are all declared as Integer.

Hope this helps.

Re: Auto increment an ID if other ID exists

Anonymous — Thu, 16 Nov 2017 09:42:15 GMT

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

4 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

2017-11-16_102928.jpg
2017-11-16_104133.jpg

Re: Auto increment an ID if other ID exists

TRF — Thu, 16 Nov 2017 09:57:54 GMT

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?

Re: Auto increment an ID if other ID exists

Anonymous — Thu, 16 Nov 2017 10:13:03 GMT

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

Re: Auto increment an ID if other ID exists

TRF — Thu, 16 Nov 2017 10:32:08 GMT

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.

Re: Auto increment an ID if other ID exists

Anonymous — Thu, 16 Nov 2017 12:20:45 GMT

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.

Re: Auto increment an ID if other ID exists

TRF — Thu, 16 Nov 2017 12:44:53 GMT

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

Re: Auto increment an ID if other ID exists

Anonymous — Thu, 16 Nov 2017 12:53:10 GMT

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

2017-11-16_135239.jpg

Re: Auto increment an ID if other ID exists

TRF — Thu, 16 Nov 2017 13:42:10 GMT

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.

Re: Auto increment an ID if other ID exists

Anonymous — Fri, 17 Nov 2017 14:52:14 GMT

Hi,

I've succeeded with your link

Thank you.

Best regards.

Re: Auto increment an ID if other ID exists

TRF — Sat, 18 Nov 2017 07:53:00 GMT

Great!