Hi,
I need to increment a progressive number during insertion.
Thi is the query
" INSERT INTO giri
(idBatt, idNome, tempo, prog, usRx, giro, I1, I2, I3, I4, V1, V2)
VALUES
(:idBatt, :idNome, :tempo, COALESCE( (SELECT MAX(prog) FROM giri WHERE idBatt = :idBatt GROUP BY idBatt),0) +1, 0, :giro, :I1, :I2, :I3, :I4, :V1, :V2)"
The query is called in a loop and if idNome does not change the value of prog is correct, if idNome changes sometime during the insertion in the loop the value of prog sometimes remains the same and sometimes changes.
DataB->Q_salvaGiro->ParamByName("idBatt")->Value= idMerge;  //always the same
DataB->Q_salvaGiro->ParamByName("idNome")->Value= idNome;
DataB->Q_salvaGiro->ParamByName("tempo")->Value= pq->FieldByName("tempo")->AsFloat;
DataB->Q_salvaGiro->ParamByName("giro")->Value   = pq->FieldByName("giro")->AsInteger;
DataB->Q_salvaGiro->ParamByName("I1")->Value   = pq->FieldByName("I1")->AsFloat;
...
DataB->Q_salvaGiro->ExecSQL();
Attached the result
What Im wrong?
Thanks!

Mirco


Without your data to test my guess is that you're running into a problem with table refresh. You could test by refreshing the table concerned in the loop before inserting a new row. If that works then just do it before idNome changes. If not can you build a small test application for us to have a look at?

I presume the value of idBatt can change between runs even though you say

DataB->Q_salvaGiro->ParamByName("idBatt")->Value= idMerge;  //always the same

otherwise this

COALESCE( (SELECT MAX(prog) FROM giri WHERE idBatt = :idBatt GROUP BY idBatt),0) +1

could be simplified to

COALESCE( (SELECT MAX(prog) FROM giri,0) +1

Roy Lambert

Yes the GROUP BY is added to try to resolve.
Thanks I try to investigate further

ok damn my fault
I left a trigger hidden in the corner when i copied the table
Sorry for the waste of time

Mirco


Thanks for that - I needed a good laugh right now.

Roy Lambert