Problem with Data Types/Views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Data Types/Views

Author

Topic

willystyle04
Starting Member

3 Posts

Posted - 2010-11-02 : 14:59:50

Hi All,

I need the help of you SQL Gurus out there...

I've got a few tables:

Responses
RID (int, PK)
QID (int, FK)
RVal (nvarchar(max))

Questions
QID (int, PK)
QText (nvarchar(100))
ValType (nchar(3))

RVal and ValType are the key fields here...RVal can contain any one of three types of answers - NUM (for numeric values), TXT (for text), and BOL (for boolean T/F)

The problem is, I need to average those RVals for a each Question ID (QID) in a set of question IDs that are all RBL:

SELECT AVG(CAST(RVal As Int)), QID
FROM Responses r INNER JOIN Questions q on r.QID = q.QID
WHERE q.ValType = 'NUM'
GROUP BY r.QID

The issue is that I get an error converting certain values like 'True' to int, even though I'm limiting it to just NUM type questions in the WHERE clause.

So I figured a view would solve this...I extracted out a view and selected all the NUM questions, casting their RVals to int in the select statement...so now I have:

View
QID (int, FK)
RID (int, PK)
RVal (int)

If I run the same query as above, I get the SAME ERROR about converting a non-int value to int...but it's already an int! This is incredibly frustrating.

I have done due diligence to ensure there aren't any questions that are mis-labeled as NUM but are actually something else. In the view, every value that appears is indeed a number...

I'm lost as to next steps...Help!

Thanks everyone!

Kristen
Test

22859 Posts

Posted - 2010-11-02 : 15:33:40

There is a non-numeric value in your RVal column somewhere.

Try


SELECT TOP 100 *
FROM Responses AS r 
    JOIN Questions AS q 
        ON q.QID = r.QID
       AND q.ValType = 'NUM'
WHERE r.RVal LIKE  '%[^0-9]%'

willystyle04
Starting Member

3 Posts

Posted - 2010-11-02 : 15:48:49

Thanks for the reply, and I tried the code you suggested. It didn't return any results on the actual table, but I did check through the view again to verify those were all INTs and then I ran your code on the view...and it returns this error:

Conversion failed when converting the nvarchar value 'Yes' to data type int.

Which is the error I keep getting. This led me to believe the issue was exactly as you suggested, however...I've check the view I don't even know how many times at this point, and they are all numbers from 0 to 5 in that column...unless one is a boolean value disguised as a number somehow...but it seems unlikely SQL Server could do that considering the column data type is INT (on the view).

What am I missing?

Kristen
Test

22859 Posts

Posted - 2010-11-02 : 18:12:55

Looking at the data won't do, you need to have SQL find the data that is not numeric. There might be a rogue character in the column - which perhaps displays the same as SPACE - so you won't see it even if you look!

Find the column value which is NOT [0-9]

Start with the source code for your View - because that is referencing the field which is "yes" or "true" - and put my LIKE test in to find the non-numeric value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources