CODEWITHSUNDEEP
sqlsql-serverview
Robert Fraire
Robert Fraire

Reputation: 1

Error when summing converted column

I have a table in SQL Server that contains 2 columns: Question_asked, Response

The Response column is of the datatype varchar(500) and holds the users response to the question that is held in the column Question_asked

I have created a view V_age_attr with the following SQL statement:

select 
    question_asked, cast(Response as integer) 
from 
    main_table 
where 
    question_asked = 'What is your age'

If I run a simple SQL query the results return as expected. But in some circumstances like when using a sum/group by query. I get an error msg (actual msg I get):

Conversion failed when converting the varchar value '2011-08-13 00:00:00' to data type int

In the table, main_table, one of the questions is the start_date, so the column Response does hold date values in the table, but not in the view I created.

Again, I can run a select query and check all the values for my cast(Response as integer) and they all are integer values.

My main question is: can anyone tell me if this is known/expected behavior in SQL Server 2008 R2, and/or does anyone else have another way to get a subset of values from this type of table other than creating a view?

Thank you

Upvotes: 0

Views: 89

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Given the bug that Martin has highlighted, I would actually use the original condition in the CASE statement, which will apply to any numeric type, not limited to Gordon's int interpretation. Better than ISNUMERIC, but TRY_CONVERT would be better from SQL Server 2012 onwards. You would hope SQL Server can re-use the condition resolution in the WHERE clause to assist in the SELECT, or vice-versa.

select 
    question_asked, case when question_asked = 'What is your age'
                         then cast(Response as integer)
                         end
from 
    main_table 
where 
    question_asked = 'What is your age'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Alas, filtering by isnumeric doesn't necessarily work. SQL is a descriptive language, not a procedural language. So, the operations do not necessary work in the order specified.

In this case, SQL Server will attempt to do the conversion when reading the data and then apply the filter. Too late. You have an error.

The case statement is the only statement that guarantees order of evaluation, at least when aggregation functions are not involved. The following should fix the conversion problem:

select question_asked,
       (case when isnumeric(Response) = 1 and
                  Response not like '%.%'
             then cast(Response as int)
        end) as Response
from main_table
where question_asked = 'What is your age'

This version also checks for a decimal point in Response. ISNUMERIC will return 1 when there is a decimal point, but that will fail the conversion as well.

Upvotes: 3

eouw0o83hf
eouw0o83hf

Reputation: 9588

Filter using ISNUMERIC - there may be non-integral data as responses which are messing up the aggregate functions.

SELECT
 question_asked,
 CASE(response AS INT)
FROM
 main_table
WHERE
 question_asked = 'What is your age'
 AND ISNUMERIC(response) = 1

Edit: if that still doesn't work, you may need to do some more aggressive type-checking on your response column. Check out the responses here:

How to get if a string is a number in T-SQL

Upvotes: 0

Related Questions