Link to home
Start Free TrialLog in
Avatar of Mystical_Ice
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_WorkOrderOprtHistDetail 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
SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial