May 22, 2009 at 9:48 am
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:
[font="Courier New"]Models
ModelID (identity)
ProductID (int)
ModelName (nvarchar(30))
ModelDescription (nvarchar(128))[/font]
[font="Courier New"]ModelAttributes
AttributeID (identity)
ModelID (int)
AttributeID (int)
Value (nvarchar(128))[/font]
Query:
[font="Courier New"]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)[/font]
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
May 22, 2009 at 1:33 pm
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
Consultant - Straight Path Solutions
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
May 22, 2009 at 2:14 pm
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
May 26, 2009 at 6:02 am
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
[font="Courier New"]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)[/font]
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
May 26, 2009 at 6:54 am
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
Consultant - Straight Path Solutions
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
May 26, 2009 at 12:08 pm
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...
[font="Courier New"]Temp_Models
ModelID (identity)
ProductID (int)
Description (nvarchar(128))
UnitCost
Temp_Attributes
Temp_AttributeID (identity)
ModelID (int)
AttributeID (int)
Value (nvarchar(128))[/font]
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)
[font="Courier New"]
ModID ProdID Desc. Cost AttrID Value
582A3729853744.247.005
582A3729853744.24140
582A3729853744.241511.5
582A3729853744.24190
582A3729853744.2420128500
582A3729853744.242116
582A3729853744.242256900
582A3729853744.242323
582A3729853744.2424C29
582A3729853744.242554
582A3729853744.2426
582A3729853744.2429IL-60803
------------------------------------------------
592A373080299.047.06
592A373080299.04140
592A373080299.0415.75
592A373080299.04190
592A373080299.0420150114
592A373080299.042116
592A373080299.0422111389
592A373080299.042322
592A373080299.0424C32
592A373080299.042554
592A373080299.0426
592A373080299.0429IL-30803
------------------------------------------------
602A373163728.887.006
602A373163728.88140
602A373163728.8815.75
602A373163728.88190
602A373163728.8820169400
602A373163728.882116
602A373163728.8822116200
602A373163728.882322
602A373163728.8824C35
602A373163728.882554
602A373163728.8826
602A373163728.8829IL-60803
------------------------------------------------
612A3825574733.527.0401
612A3825574733.52140
612A3825574733.52153.56
612A3825574733.52190
612A3825574733.5220120100
612A3825574733.522116
612A3825574733.522247900
612A3825574733.52234
612A3825574733.5224B86
612A3825574733.522554
612A3825574733.5226
612A3825574733.5229IL-60803
[/font]
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.
[font="Courier New"]
ModID ProdID Desc. Cost AttrID Value
582A3729853744.2429IL-60803
592A373080299.047.06
592A373080299.0429IL-30803
602A373163728.8829IL-60803
612A3825574733.527.0401
612A3825574733.5229IL-60803
[/font]
I hope that this is clearer than previous posts.
Thanks again for your time an suggestions.
Chuck
May 26, 2009 at 12:38 pm
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.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
Consultant - Straight Path Solutions
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply