September 4, 2009 at 10:47 am
Hello, I know I'm probably missing something obvious here, but why would this code work...
Select *
from mpp.dbo.dmItemCalc
Where DM_ID in (
Select DM_ID From mpp.dbo.dmItemCalc
Group by DM_ID Having Sum(BegInv)=0 and Sum(PO)=0 and Sum(Foro)=0 and Sum(Fcst)=0 and Sum(Sched)=0
)
and DM_ID not in (
Select DM_ID From dw.dbo.dwItemMap Where Class in (1,6,8)
)
but this fails...
Delete
from mpp.dbo.dmItemCalc
Where DM_ID in (
Select DM_ID From mpp.dbo.dmItemCalc
Group by DM_ID Having Sum(BegInv)=0 and Sum(PO)=0 and Sum(Foro)=0 and Sum(Fcst)=0 and Sum(Sched)=0
)
and DM_ID not in (
Select DM_ID From dw.dbo.dwItemMap Where Class in (1,6,8)
)
It gives the error "SQL Server Database Error: Arithmetic overflow error converting expression to data type int."
September 4, 2009 at 11:02 am
Have you tried running the sub-selects separately to see what you get from those?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:09 am
Yes. They both work fine. I just changed the query to cast the sum values as float before I sum them, and that avoids the error. So it has something to do with the way SQL server is dealing with the Sum values on a Delete statement. And it is different than select.
Thanks for your reply, I'd definitely be interested in learning why it is doing this, even though I have resolved the issue for now.
Mike
September 4, 2009 at 11:21 am
Good that you solved it.
It's probably a minor difference in the way the engine code was written for selects vs for deletes. But that's just me guessing. You might want to bring it up on Microsoft's pages. connect.microsoft.com has a place to mention bugs or request features.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply