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

avatar image

n3w2sql
870 22 32 39

(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

avatar image

WilliamD
26.2k 18 34 48

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.

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:

x2076

asked: Aug 08, 2012 at 01:08 PM

Seen: 3486 times

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

Copyright 2016 Redgate Software. Privacy Policy