Below is the query which gives the error records. SELECT Count (*) FROM (SELECT cc_bulkinvoice.id FROM cc_bulkinvoice WHERE cc_bulkinvoice.status NOT IN ( 17, 23 ) AND (SELECT ( CASE WHEN Sum (bii.amount) IS NOT NULL THEN Sum (bii.amount) ELSE 0 END ) FROM cc_bulkinvoiceitem bii WHERE bii.bulkinvoiceid = cc_bulkinvoice.id AND bii.retired = 0) <> cc_bulkinvoice.totaltransactionamount) a

Below is the sql update script to resolve the error records: USE ClaimCenter;

begin transaction
update cc_bulkinvoice
set cc_bulkinvoice.TotalTransactionAmount = t2.sumAmt
from cc_bulkinvoice b  inner join (
select bii.BulkInvoiceID,sum(bii.Amount) as sumAmt from cc_bulkinvoiceitem bii
where bii.Retired = 0
group by bii.BulkInvoiceID
) as t2 on t2.BulkInvoiceID = b.ID
where b.id in (26841,26849,26850,26856,13036,13037,2003,2214)

      
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;       

Even after running the db script to update the records, the query is still returning error records. Could anyone please help in identifying what is wrong with the update script.

1

There are 1 answers

1
SteveDrippsCentricConsulting On

The error in your SQL script is not immediately obvious to me. The way I would go about troubleshooting this would be to one-by-one manually figure out what the bulk invoice total should be on the cc_bulkinvoice parent table should be and then manually add-up the non-retired cc_bulkinvoiceitem rows and see what number you get.

The one thing that the query to find the bad bulk invoices does that your update query does not do is a null check. So if for example one of your bulk invoices was like this:

cc_bulkinvoice.TotalTransactionAmount != 0

but...

cc_bulkinvoice.InvoiceItems.Count == 0
//might need to add in a check to the retired property of the InvoiceItems

Then your update query will not work because it does not work in the case where there are 0 non-retired invoice items