SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Failed to Convert nvarchar to smallint
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ywb
Yak Posting Veteran

55 Posts

Posted - 09/24/2012 :  18:41:27  Show Profile  Reply with Quote
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

5155 Posts

Posted - 09/24/2012 :  21:04:53  Show Profile  Reply with Quote
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.
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 09/25/2012 :  00:05:08  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Enhanced Is Numeric function

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

ywb
Yak Posting Veteran

55 Posts

Posted - 09/25/2012 :  17:50:55  Show Profile  Reply with Quote
Thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000