|
ywb
Yak Posting Veteran
52 Posts |
 Posted - 09/24/2012 : 18:41:27
|
Hi,
I have a question table that stores questions, which expect answer to be either in text or integer, and an answer table like this:
CREATE TABLE #TempQuestion (queestionID smallint PRIMARY KEY, theType nvarchar(10));
CREATE TABLE #TempAnswer (answerID smallint IDENTITY(1, 1) PRIMARY KEY, questionID smallint, answer nvarchar(10));
INSERT INTO #TempQuestion (queestionID, theType) VALUES (1, 't');
INSERT INTO #TempQuestion (queestionID, theType) VALUES (2, 'i');
INSERT INTO #TempAnswer (questionID, answer) VALUES (1, 'abc');
INSERT INTO #TempAnswer (questionID, answer) VALUES (1, 'bcd');
INSERT INTO #TempAnswer (questionID, answer) VALUES (1, 'cde');
INSERT INTO #TempAnswer (questionID, answer) VALUES (2, '1');
INSERT INTO #TempAnswer (questionID, answer) VALUES (2, '2');
INSERT INTO #TempAnswer (questionID, answer) VALUES (2, '3');
I would like to get all the answers that are integer and are bigger than 2 with this query:
SELECT *
FROM (
SELECT q.*, a.*
FROM #TempQuestion q INNER JOIN #TempAnswer a ON q.queestionID = a.questionID
WHERE (q.theType = 'i')
) AS a
WHERE (CAST(answer AS smallint) >= 2)
ORDER BY queestionID, answerID;
But it failed with this error "Conversion failed when converting the nvarchar value 'abc' to data type smallint."
How can I get around this problem?
Thanks,
ywb
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/24/2012 : 21:04:53
|
Change your WHERE clause as shown below:WHERE (CASE WHEN ISNUMERIC(answer) = 1 THEN CAST(answer AS smallint) END >= 2)
HOWEVER:
1. The ISNUMERIC function is not fool-proof. If you had a value of 'e' in a row for answer, it would return isnumeric = 1 but will fail when you try to convert it to smallint.
2. Mixing the two different data types in one column is not a recommended design. Without having thought through it much, at the very least, keep numeric and non-numeric answers in two columns and perhaps have an indicator column which tells you what type of answer is expected. |
 |
|
Failed to Convert nvarchar to smallint
