Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
MAX() in insert query |
Thu, Jul 21 2022 2:33 AM | Permanent Link |
Mirco Malagoli | 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! Attachments: SQLResult.csv |
Thu, Jul 21 2022 5:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Jul 21 2022 10:12 AM | Permanent Link |
Mirco Malagoli | Yes the GROUP BY is added to try to resolve.
Thanks I try to investigate further |
Thu, Jul 21 2022 11:37 AM | Permanent Link |
Mirco Malagoli | ok damn my fault
I left a trigger hidden in the corner when i copied the table Sorry for the waste of time |
Fri, Jul 22 2022 3:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mirco
Thanks for that - I needed a good laugh right now. Roy Lambert |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |