x

Operand data type varchar is invalid for subtract operator.

I am trying to do a subtraction but Im getting an error and dont know why. CAn anyone advise what is wrong with my code. thanks in advance if anyone can see what I am doing wrong.

SELECT 
      [POLICYNUM]
      ,[IsActual]
      ,[REINSURED]
      ,CASE WHEN [YearOfAccount] <> [AccountYear] THEN COUNT([REINSURED]) ELSE '0' END AS [ACCOUNTYEARERRORS]
      ,[YearOfAccount]
      ,[AccountYear]
      ,CASE WHEN [InceptionDate] <> [IncpDt] THEN COUNT([REINSURED]) ELSE '0' END AS [INCEPTIONERRORS]
      ,[InceptionDate]
      ,[IncpDt]
      ,CASE WHEN [ExpiryDate] <> [ExpyDt] THEN COUNT([REINSURED]) ELSE '0' END AS [EXPIRYERRORS]
      ,[ExpiryDate]
      ,[ExpyDt]
      ,CASE WHEN [PremiumsCurrency] <> [S2000PremiumsCurrency] THEN COUNT([REINSURED]) ELSE '0' END AS [CCYERRORS]
      ,[PremiumsCurrency]
      ,[S2000PremiumsCurrency]
      ,CASE WHEN [PremiumsCurrency] <> [S2000PremiumsCurrency] THEN COUNT([REINSURED]) ELSE '0' END AS [PREMIUMERRORS]
      ,[ALPSPremium]
      ,[PREMIUM]
      ,CASE WHEN [PremiumsCurrency] <> [S2000LIMITCUR] THEN COUNT([REINSURED]) ELSE '0' END AS [LMTCCYERRORS]
      ,[LIMITCUR]
      ,[S2000LIMITCUR]
       ,CASE WHEN [Limit] <> [S2000LIMIT] THEN COUNT([REINSURED]) ELSE '0' END AS [LIMITERRORS]
      ,[Limit]
      ,[S2000LIMIT]
      ,[LossesCurrency]
      ,[S2000LOSSESCUR]
      ,[EXCESSCUR]
      ,[AggregateDeductible]
      ,[LinePercent]
      ,[CalcLn]
      ,sum([LinePercent]-[CalcLn]) calcdiff
      ,CASE WHEN [S2000HOWMANYGRP1] <> [HOWMANYGRP1] THEN COUNT([REINSURED]) ELSE '0' END AS [HMGR1ERRORS]
      ,[HOWMANYGRP1]
      ,[S2000HOWMANYGRP1]
       ,CASE WHEN [HOWMANYGRP2] <> [S2000HOWMANYGRP2] THEN COUNT([REINSURED]) ELSE '0' END AS [HMGR2ERRORS]
      ,[HOWMANYGRP2]
      ,[S2000HOWMANYGRP2]
       ,CASE WHEN [RPPERCENTGRP1] <> [S2000RPPERCENTGRP1] THEN COUNT([REINSURED]) ELSE '0' END AS [RPGR1ERRORS]
      ,[RPPERCENTGRP1]
      ,[S2000RPPERCENTGRP1]
       ,CASE WHEN [RPPERCENTGRP2] <> [S2000RPPERCENTGRP2] THEN COUNT([REINSURED]) ELSE '0' END AS [RPGR2ERRORS]
      ,[RPPERCENTGRP2]
      ,[S2000RPPERCENTGRP2]
      ,CASE WHEN [ExsAmt] <> [EXCESSTOTAL] THEN COUNT([REINSURED]) ELSE '0' END AS [EXCESSERRORS]
      ,[UnderlyingLimit]
      ,[UNLExcess]
      ,[ExsAmt]
      ,[EXCESSTOTAL]
   FROM [LEE].[dbo].[TREATYERRORS]

   GROUP BY
   [POLICYNUM]
      ,[IsActual]
      ,[REINSURED]
      ,[YearOfAccount]
      ,[AccountYear]
      ,[InceptionDate]
      ,[IncpDt]
      ,[ExpiryDate]
      ,[ExpyDt]
      ,[PremiumsCurrency]
      ,[S2000PremiumsCurrency]
      ,[ALPSPremium]
      ,[PREMIUM]
      ,[LIMITCUR]
      ,[S2000LIMITCUR]
      ,[Limit]
      ,[S2000LIMIT]
      ,[LossesCurrency]
      ,[S2000LOSSESCUR]
      ,[EXCESSCUR]
      ,[AggregateDeductible]
      ,[LinePercent]
      ,[CalcLn]
      ,[HOWMANYGRP1]
      ,[S2000HOWMANYGRP1]
      ,[HOWMANYGRP2]
      ,[S2000HOWMANYGRP2]
      ,[RPPERCENTGRP1]
      ,[S2000RPPERCENTGRP1]
      ,[RPPERCENTGRP2]
      ,[S2000RPPERCENTGRP2]
      ,[UnderlyingLimit]
      ,[UNLExcess]
      ,[ExsAmt]
      ,[EXCESSTOTAL]
more ▼

asked Aug 08, 2012 at 01:08 PM in Default

n3w2sql gravatar image

n3w2sql
850 14 24 33

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The error message seems pretty clear - you are subtracting on a varchar and this is not a supported operation.

Is the object [LEE].[dbo].[TREATYERRORS] a view that is doing something wrong?

To identify where this is happening, If you don't know your data types on the columns you are selecting you could start by reducing the columns in the select and add them back in one column at a time.
more ▼

answered Aug 08, 2012 at 01:16 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

my guess would be its one of the columns in this sum([LinePercent]-[CalcLn]) calcdiff
Aug 08, 2012 at 01:18 PM Fatherjack ♦♦

it is [LinePercent] and [CalcLn]) that are showing as varchar. so do i need to convert these to int to get my sum to work?

Aug 08, 2012 at 01:19 PM n3w2sql
yes indeed. You can do the sum "5 - 3" but you cant do the sum "Orange - Apple". The server doesnt know that you are actually trying to do the sum "six - two" so changing it to "6 - 2" will let it do the maths.
Aug 08, 2012 at 01:22 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840

asked: Aug 08, 2012 at 01:08 PM

Seen: 2133 times

Last Updated: Aug 08, 2012 at 01:22 PM