Mystical_Ice
asked on
SQL query help!
Hi,
I'm trying to run a query to SUM the results of a case statement. Below is the query; it pulls results just fine, but when I put a 'SUM' around the case statement, I get the following error:
"Operand data type nvarchar is invalid for sum operator."
The column type I'm doing the sum against is nvarchar(200).
I've tried to convert, but not sure I'm doing it right. All the values in the table (at least for the range I'm running) are actual values (5814.000, 405, 11802, 686.31 ,etc)
Here's the script if you'd like to see it. Basically there are two data values in a table - machine weight and forge weight. the case statement is picking the value of the machine weight ('d') unless there's no value, in which case it uses forge weight ('e')
select
--a.EndTime,
--a.ComplQuantity,
--b.UsrDfnId as 'Workorder',
--b.DrawName,
--c.UsrDfnId as 'Operation',
--d.DataValue as 'Machine Weight',
--e.DataValue as 'Forge Weight',
case when d.DataValue IS NULL then e.DataValue
else e.DataValue end as 'Weight'
from COSS_WorkOrderOprtHistDeta il a
left outer join coss_workorder b
on a.WorkOrderIdent = b.ident
left outer join COSS_Operation c
on a.OprtIdent = c.ident
left outer join COSS_CustomAttributeValue d
on b.UsrDfnId = d.OwnerUsrDfnId and d.AttribName = 'Machine Weight'
left outer join COSS_CustomAttributeValue e
on b.UsrDfnId = e.OwnerUsrDfnId and e.AttribName = 'Forge Weight'
where a.EndTime between '2015-07-05' and '2015-07-11' and b.CustomerName not like 'Internal%' and c.UsrDfnId = 'FORGE' and a.ComplQuantity > '0'
order by b.UsrDfnId
I'm trying to run a query to SUM the results of a case statement. Below is the query; it pulls results just fine, but when I put a 'SUM' around the case statement, I get the following error:
"Operand data type nvarchar is invalid for sum operator."
The column type I'm doing the sum against is nvarchar(200).
I've tried to convert, but not sure I'm doing it right. All the values in the table (at least for the range I'm running) are actual values (5814.000, 405, 11802, 686.31 ,etc)
Here's the script if you'd like to see it. Basically there are two data values in a table - machine weight and forge weight. the case statement is picking the value of the machine weight ('d') unless there's no value, in which case it uses forge weight ('e')
select
--a.EndTime,
--a.ComplQuantity,
--b.UsrDfnId as 'Workorder',
--b.DrawName,
--c.UsrDfnId as 'Operation',
--d.DataValue as 'Machine Weight',
--e.DataValue as 'Forge Weight',
case when d.DataValue IS NULL then e.DataValue
else e.DataValue end as 'Weight'
from COSS_WorkOrderOprtHistDeta
left outer join coss_workorder b
on a.WorkOrderIdent = b.ident
left outer join COSS_Operation c
on a.OprtIdent = c.ident
left outer join COSS_CustomAttributeValue d
on b.UsrDfnId = d.OwnerUsrDfnId and d.AttribName = 'Machine Weight'
left outer join COSS_CustomAttributeValue e
on b.UsrDfnId = e.OwnerUsrDfnId and e.AttribName = 'Forge Weight'
where a.EndTime between '2015-07-05' and '2015-07-11' and b.CustomerName not like 'Internal%' and c.UsrDfnId = 'FORGE' and a.ComplQuantity > '0'
order by b.UsrDfnId
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.