Error converting data type varchar to numeric using a case function

Question

When I select the table

Select 
    [Premium Amount], [Policy Number], 
    [Transaction Type Code], Insured, * 
from 
    DW.Table
where 
    [Policy Number] in ('1111111111', '2222222222')

these are the results.

Premium Amount  Policy Number   Transaction Type Code      Insured
-12798.0000     1111111111       Cancellation              ABC Group
27000.0000      1111111111       Renewal Business          ABC Group 
-8452.7700      2222222222       Cancellation              DEF Group
10221.0000      2222222222       Renewal Business          DEF Group

What I want to happen is to create two new columns, [Original Policy Premium] and [Transaction Premium].

[Original Policy Premium] is a column that contains when the Transaction Type Code = Renewal Business and it will result the corresponding Premium Amount = 27000.0000 for Policy Number 111111111. Same applies to Policy Number 22222222.

Make it look like this.

Original Policy Premium  Transaction Premium  Policy Number   
27000.0000                -12798.0000          1111111111
10221.0000                -8452.7700           2222222222

Select 
    case 
       when [Transaction Type Code] = 'Renewal Business' 
         then [Premium Amount] 
         else '' 
    end as [Original Policy Premium],
    case 
      when [Transaction Type Code] = 'Cancellation' 
        then [Premium Amount] 
        else '' 
    end as [Transaction Premium],
    [Policy Number], 
    [Transaction Code] 
from 
    DW.Table
where 
    [Policy Number] in ('111111111', '22222222')

But I get this error after running.

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

But when I replace END as '' with END AS NULL

Original Policy Premium  Transaction Premium   Policy Number   
    27000.0000                NULL             1111111111
    NULL                     -12798.0000       1111111111
    10221.0000                NULL             2222222222
    NULL                     -8452.7700        2222222222

Thank you. your answer is correct. But please look at my edits. — BongReyes
– BongReyes, Commented Jul 7, 2015 at 21:39

Giorgi Nakeuri · Accepted Answer · 2015-07-07 21:41:41Z

1

Your case paths return different types. They must be of same type. Change to:

select max(case when [Transaction Type Code] = 'Renewal Business'
                then [Premium Amount] 
                else null end) as [Original Policy Premium],
       max(case when [Transaction Type Code] = 'Cancellation'
                then [Premium Amount] 
                else null end) as [Transaction Premium],
       [Policy Number]
from table
where [Policy Number] in ('111111111', '22222222')
group by [Policy Number]

edited Jul 7, 2015 at 21:41

answered Jul 7, 2015 at 21:36

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BongReyes Over a year ago

SUPERB! But how does the MAX function and Group by help do the trick? Using max will only get the highest number, but there's no other number higher than 27000.0000 or 10221.0000? These numbers are unique. So how does it help to fix it?

Giorgi Nakeuri Over a year ago

It is called conditional aggregation and standart trick. For example for 1111111111 you have one cancellation and one renewal. Look at cancellation case: it will produce set - -12798.0000 and null. Max will pick -12798.0000 . Same for renewal. Same for 2222222222.

BongReyes Over a year ago

Now I get it. Thank you.

Collectives™ on Stack Overflow

Error converting data type varchar to numeric using a case function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related