May 30, 2007 at 9:36 am
We just started getting an error 'Arithmetic overflow error converting expression to data type int' (on our QA db) on code that has been running in production for a long time (written several years ago by a long-gone contractor). The error is coming from the delete trigger code below, presumably the SUM(). I am still trying to decipher what exactly this code is trying to accomplish, but it has to do with processing records having a NULL synonymID. It appears we have finally reached data that the sum of synonymIDs exceeds the int capability of the SUM() fn. Assuming I am on the right track, it's only a matter of time before this happens in Prod.
Does anyone have suggestions how to re-write this w/o using the SUM() fn?
Delete mainTable
From mainTable mt,
(
Select d.formID, dm.drugID, Sum(IsNull(fd.synonymID, 0)) as drugID_count
From deleted d
join drugMap dm on (dm.synonymID = d.synonymID)
join drugMap dm2 on (dm2.drugID = dm.drugID)
left join formDrugs fd on (fd.synonymID = dm2.synonymID and fd.formID = d.formID)
Group by d.formID, dm.drugID
having Sum(IsNull(fd.synonymID, 0)) = 0
) no_drugs
Where mt.formID = no_drugs.formID
and mt.drugID = no_drugs.drugID
May 30, 2007 at 9:49 am
It looks like you don't use the Sum(IsNull(fd.synonymID, 0)) as drugID_count in the SELECT so I would remove it.
Then you could try to replace the having Sum(IsNull(fd.synonymID, 0)) = 0
with having Sum(IsNull(CAST(CAST(fd.synonymID AS BIT) AS INT), 0)) = 0
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 30, 2007 at 10:12 am
Looks promising, thanks! ... will do more testing...
May 30, 2007 at 11:49 am
-First make sure that
fd.synonymID
dm.synonymID
d.synonymID
dm2.synonymID
are all of type *int*
-Secondly if your sum is bigger than an INT the cast should be made to BIGINT.
Cheers,
* Noel
May 30, 2007 at 7:18 pm
Why SUM???
Why not simple COUNT?
And you don't need ISNULL for COUNT.
_____________
Code for TallyGenerator
May 30, 2007 at 7:25 pm
I believe this would be cleaner and faster option:
Delete mt
From mainTable mt
inner join deleted d on d.formID = mt.formID
inner join drugMap dm on dm.synonymID = d.synonymID AND mt.drugID = dm.drugID
inner join drugMap dm2 on dm2.drugID = dm.drugID
WHERE NOT EXISTS (select 1 from dbo.formDrugs fd
where fd.synonymID = dm2.synonymID and fd.formID = d.formID )
Please check carefully the logic of joins.
And if you qualify your objects with owner name (I guess it would be "dbo.") then SQL Server would not need to recompile your SP every time you call it.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy