August 1, 2006 at 4:29 am
Hi guys,
I have a problem with CAST function.
The query looks like this:
SELECT (CASE q.[answer_type] WHEN 2 THEN CAST(u.[content] AS VARCHAR) ELSE CAST(1 AS BIT) END) AS [selected]
FROM UserAnswers u, Question q
WHERE u.[user_id] = 2 AND u.[question_id] = q.[question_id]
Column u.[content] is varchar(max). So the idea is whether the type of the question is 2 return varchar else return true. But this query every time returns bit (e.g. content is 123 and is converted to 1).
Thanks for help
August 1, 2006 at 8:18 pm
My guess would be that you can only return a single datatype option for a given column (derived or otherwise) and the second CAST statement is taking precedence.
Also, it appears that the u.[content] field is always returning a numeric - which SQL believes it can cast to bit field.
When I tried this with a genuine varchar field it got an error as shown below
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'blah' to data type bit.
Does the datatype returned actually matter, or is the value more important? You may need to reconsider how you approach this issue.
August 2, 2006 at 1:51 am
yes, you are right. CASE is able to return only one datatype and bit has higher precedence than varchar.
So I rewrote the query without CASE function.
Thanks
August 2, 2006 at 9:42 am
The idea that a column has the same datatype for every row is pretty fundamental. If you really wanted a column to be VARCHAR in some rows and BIT in others you could cast them both to SQL_VARIANT, but I doubt that you'll be happy with the results.
Are you aware that CAST(xxx AS VARCHAR) is really CAST(xxx AS VARCHAR(30))? If all your data fits into VARCHAR(30), then why the heck are you using VARCHAR(MAX)?
August 3, 2006 at 2:13 am
This query was a little bit simplified.
But it is not possible to use sql_variant and varchar(max).
August 3, 2006 at 2:48 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply