Unable to sum correctly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unable to sum correctly

Author

Topic

wjohnson16
Starting Member

5 Posts

Posted - 2009-06-18 : 09:32:09

ERROR MESSAGE**Operand data type nvarchar is invalid for sum operator.***

SELECT
distinct
SD.ERP_ORDER,
SH.SHIPMENT_ID,
SH.INTERNAL_SHIPMENT_NUM,
SC.ITEM,
SC.LOT,
SN.SERIAL_NUMBER,
--SC.QUANTITY,
SUM(SC.QUANTITY_UM)as Total,<---This is the problem
SH.SHIP_TO_NAME
FROM
SHIPPING_CONTAINER SC WITH(NOLOCK)
JOIN SHIPMENT_DETAIL SD
ON SC.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
JOIN SHIPMENT_HEADER SH
ON SC.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM
JOIN SERIAL_NUMBER_VIEW SN
ON SN.SHIP_CONT_NUM = SC.INTERNAL_CONTAINER_NUM
WHERE
SH.INTERNAL_SHIPMENT_NUM = '182823'
AND SD.ITEM IN ('X0587100','X0587200','X0587500','X0587000','X0586600','X0586800'
,'X0586700','X0586900','X0587600','X0587400','X0587300')
AND SC.LOT >= '1208'
AND SN.SERIAL_NUMBER IS NOT NULL
--AND SC.CONTAINER_ID IS NOT NULL
GROUP BY
SD.ERP_ORDER,
SH.SHIPMENT_ID,
SH.INTERNAL_SHIPMENT_NUM,
SC.ITEM,
SC.LOT,
SN.SERIAL_NUMBER,
--SC.QUANTITY,
SH.SHIP_TO_NAME

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-18 : 09:47:30

This means that your SC.QUANTITY_UM field is nvarchar instead of a numeric. For example, if you have 10,000 in the field, you can't sum it, but could if it were 10000 without the comma.

Try this
select * from SHIPPING_CONTAINER where ISNUMERIC(QUANTITY_UM) = 0 and see what that returns

Jim

wjohnson16
Starting Member

5 Posts

Posted - 2009-06-18 : 10:06:39

Jim,
You was right thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources