Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error converting data type nvarchar to numeric Expand / Collapse
Author
Message
Posted Friday, May 22, 2009 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 1:12 PM
Points: 7, Visits: 24
I have a table (see below) that contains an ID column and a Value column. The ID column is an integer and the Value column is varchar. Depending on the ID the actual data stored in the Value column may be numeric or alphanumeric (string).

I am trying to run a query that depending on the ID converts the data in the Value column appropriately. However, when I run the query I get the "Error converting data type nvarchar to numeric" error.

Tables:
Models
ModelID (identity)
ProductID (int)
ModelName (nvarchar(30))
ModelDescription (nvarchar(128))


ModelAttributes
AttributeID (identity)
ModelID (int)
AttributeID (int)
Value (nvarchar(128))


Query:
SELECT M.modelid
FROM dbo.Models AS M
INNER JOIN dbo.ModelAttributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(MA.AttributeID = 29 AND (CONVERT(NVARCHAR(128),MA.Value) LIKE '%IL%') )
OR
(MA.AttributeID = 7 AND (CONVERT(DECIMAL(10,5),MA.Value) >= 0.0360 AND CONVERT(DECIMAL(10,5),MA.Value) <= 0.0840))
AND
(M.ProductID = 2)


If I comment out the "MA.AttributeID = 29..." line it runs.
If I comment out the "MA.AttributeID = 7..." line it runs.

It seems that when I have a combination of datatypes in the Values column that the problem arrises. My guess is that the SQL interpreter is trying to convert the data in the Values column for all rows based on the first data type it encounters.

Does anyone have a way around this? All suggestions welcome!

Thanks,
Chuck
Post #722158
Posted Friday, May 22, 2009 1:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 11,264, Visits: 13,022
SQL Server is doing the conversion BEFORE any filtering. Since you are OR'ing the criteria I would recommend using UNION ALL like this:

SELECT
M.modelid
FROM
dbo.Models AS M INNER JOIN
dbo.ModelAttributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(
MA.AttributeID = 29 AND
MA.Value LIKE N'%IL%'
)AND
(M.ProductID = 2)
UNION ALL
SELECT
M.modelid
FROM
dbo.Models AS M INNER JOIN
dbo.ModelAttributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(
MA.AttributeID = 7 AND
(
CONVERT(DECIMAL(10, 5), MA.Value) >= 0.0360 AND
CONVERT(DECIMAL(10, 5), MA.Value) <= 0.0840
)
) AND
(M.ProductID = 2)

I also eliminated the unecessary conversion to NVARCHAR(128) in the first comparison. By using the conversion function you are keeping the optimizer from considering an INDEX SEEK.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #722362
Posted Friday, May 22, 2009 2:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 1:12 PM
Points: 7, Visits: 24
Jack, Thanks,I'll give it a try!!

The conversion to VARCHAR was one of my attempts to make it not try to convert but of course it didn't solve the problem.

Regards,
Chuck
Post #722391
Posted Tuesday, May 26, 2009 6:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 1:12 PM
Points: 7, Visits: 24
Jack, the UNION worked -- no errors, Thank you! However, after looking at the result I realize that I need to 'AND' the two criterial not 'OR' them.

My original query should read

SELECT M.modelid
FROM dbo.Models AS M
INNER JOIN dbo.ModelAttributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(MA.AttributeID = 29 AND (CONVERT(NVARCHAR(128),MA.Value) LIKE '%IL%') )
AND
(MA.AttributeID = 7 AND (CONVERT(DECIMAL(10,5),MA.Value) >= 0.0360 AND CONVERT (DECIMAL(10,5),MA.Value) <= 0.0840))
AND
(M.ProductID = 2)


I tried variations of UNION but I don't think that is what I need. Can you suggest an alternate to the UNION query that will work in this case?

Thanks in advance
Post #723192
Posted Tuesday, May 26, 2009 6:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 11,264, Visits: 13,022
Well, logically you can't AND the criteria together where you would like to do it. You can't have attributeid = 7 AND attributeid = 29. attributeid can only have 1 value per row. Can you post some sample data and the desired output? See the top link in my signature for an explanation of what will help me help you the most.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #723223
Posted Tuesday, May 26, 2009 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 1:12 PM
Points: 7, Visits: 24
Jack, sorry for the lack of information in my previous posts. I greatly appreciate the help you are giving. I hope this post isn't too long winded! I did look at your "Forum Etiquette" post and below are details that I think will help. It may be overkill. If so please accept my apologies...

I am trying to find a query that will return a list of DISTINCT records based on some search criteria that is actually stored in a child table. I am starting to think that I may have to do the "meat" of the comparison in code (VB.Net) instead of letting the SQL Query do it for me.

I have the following two tables...

Temp_Models
ModelID (identity)
ProductID (int)
Description (nvarchar(128))
UnitCost

Temp_Attributes
Temp_AttributeID (identity)
ModelID (int)
AttributeID (int)
Value (nvarchar(128))


Attached is a text file containing a script (SqlServerCentral_Script1.txt) to create the tables and load some test data. NOTE: I did not use temp tables, instead I prefixed each table with "Temp_"

The Temp_Models table is the parent table. For each record in the models table there will be one or more records in the Temp_Attributes table.

ModelID records represent inventory items that a company manufactures. Attributes represent characteristics of the model. Depending on the product category that a model belongs to it will have different attributes. One can think of attributes as dynamic "fields" of data.

One product category may have attributes "length", "width", "height" and "color" where another product category may have attributes like "alloy", "hardness", "finish".

Below is the sample output from a simple query that joins the two tables where ProductID = 2. I manually entered a line of dashes to visually seperate each Model's records.

SELECT
M.*, MA.AttributeID, MA.Value
FROM
dbo.Temp_Models AS M
INNER JOIN dbo.Temp_Attributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(M.ProductID = 2)


ModID ProdID Desc. Cost AttrID Value
58 2 A372985 3744.24 7 .005
58 2 A372985 3744.24 14 0
58 2 A372985 3744.24 15 11.5
58 2 A372985 3744.24 19 0
58 2 A372985 3744.24 20 128500
58 2 A372985 3744.24 21 16
58 2 A372985 3744.24 22 56900
58 2 A372985 3744.24 23 23
58 2 A372985 3744.24 24 C29
58 2 A372985 3744.24 25 54
58 2 A372985 3744.24 26
58 2 A372985 3744.24 29 IL-60803
------------------------------------------------
59 2 A373080 299.04 7 .06
59 2 A373080 299.04 14 0
59 2 A373080 299.04 15 .75
59 2 A373080 299.04 19 0
59 2 A373080 299.04 20 150114
59 2 A373080 299.04 21 16
59 2 A373080 299.04 22 111389
59 2 A373080 299.04 23 22
59 2 A373080 299.04 24 C32
59 2 A373080 299.04 25 54
59 2 A373080 299.04 26
59 2 A373080 299.04 29 IL-30803
------------------------------------------------
60 2 A373163 728.88 7 .006
60 2 A373163 728.88 14 0
60 2 A373163 728.88 15 .75
60 2 A373163 728.88 19 0
60 2 A373163 728.88 20 169400
60 2 A373163 728.88 21 16
60 2 A373163 728.88 22 116200
60 2 A373163 728.88 23 22
60 2 A373163 728.88 24 C35
60 2 A373163 728.88 25 54
60 2 A373163 728.88 26
60 2 A373163 728.88 29 IL-60803
------------------------------------------------
61 2 A382557 4733.52 7 .0401
61 2 A382557 4733.52 14 0
61 2 A382557 4733.52 15 3.56
61 2 A382557 4733.52 19 0
61 2 A382557 4733.52 20 120100
61 2 A382557 4733.52 21 16
61 2 A382557 4733.52 22 47900
61 2 A382557 4733.52 23 4
61 2 A382557 4733.52 24 B86
61 2 A382557 4733.52 25 54
61 2 A382557 4733.52 26
61 2 A382557 4733.52 29 IL-60803


A user is going to enter search criteria on a form that will specify one or more AttributeIDs from the Temp_Attributes table and for each attributeID they will specify a search value. I am currently building and executing the query for the user's search criteria in code (vb.net) due to the complexity of how the search form works. When multiple attributes have been entered as search criteria it must "AND" with the other criteria (it must meet both criteria).

If I use your original query with the union it includes models with only attribute 29 which I need to filter out. What I actually need is Models that match both the Attribute 29 criteria and the Attribute 7 criteria.


ModID ProdID Desc. Cost AttrID Value
58 2 A372985 3744.24 29 IL-60803
59 2 A373080 299.04 7 .06
59 2 A373080 299.04 29 IL-30803
60 2 A373163 728.88 29 IL-60803
61 2 A382557 4733.52 7 .0401
61 2 A382557 4733.52 29 IL-60803


I hope that this is clearer than previous posts.

Thanks again for your time an suggestions.
Chuck


  Post Attachments 
SQLServerCentral_Script1.txt (4 views, 8.29 KB)
Post #723480
Posted Tuesday, May 26, 2009 12:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 11,264, Visits: 13,022
Both of these queries return the results you expect for the sample data provided.

SELECT
*
FROM
dbo.Temp_Models AS M INNER JOIN
dbo.Temp_Attributes AS MA
ON MA.ModelId = M.ModelID
WHERE
-- either of thes will work, I prefer in
MA.AttributeID IN (7, 29)
/*
(
MA.attributeID = 29 OR
MA.attributeId = 7
)
*/
AND
(
MA.Value LIKE '%IL%' OR
MA.Value >= 0.03860 AND MA.Value <= 0.0840
) AND
M.ProductID = 2


SELECT
*
FROM
dbo.Temp_Models AS M INNER JOIN
dbo.Temp_Attributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(
MA.AttributeID = 29 AND
MA.Value LIKE N'%IL%'
)AND
(M.ProductID = 2)
UNION ALL
SELECT
*
FROM
dbo.Temp_Models AS M INNER JOIN
dbo.Temp_Attributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(
MA.AttributeID = 7 AND
(
CONVERT(DECIMAL(10, 5), MA.Value) >= 0.0360 AND
CONVERT(DECIMAL(10, 5), MA.Value) <= 0.0840
)
) AND
(M.ProductID = 2)

You may want to check out this article on Dynamic Search Conditions




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #723510
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse