Syntax error converting the nvarchar value to a column of data type int.
-
I need to do a match between two tables to get a description one field of the table 'A' is nvarchar and the other field of the table 'B' is type of integer, well supposedly the field of type nvarchar contains always data of type int but unlikely it could some data of type varchar so when that is comparate with the other field it cast a error, so i cant to do some function like convert because that will destroy the index of the table.
Syntax error converting the nvarchar value '1-9' to a column of data type int.
example select a.Desc from TableA,TableB where a.Id = b.field7
so a.Id is Int and b.field is nvarchar
well my question is if exists some set or something to avoid the crash???
for example set notype on or something similar
thanks for your help , excuse me my bad english
-
Try this
SELECT
a.Desc
FROM TableA
INNER JOIN TableB ON b.field7 = CASE
WHEN ISNUMERIC(A.ID) = 1 THEN A.ID
ELSE NULL
END
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
If you compare values with different data types you do conversion. If you don't see it it does not mean it's not there. It's just implicit, which is the worst form of CONVERT.
Conversion (implicit or explicit) will not allow to use index, so it's gonna be table scan anyway, unless you fix your table design.
As soon as there is no index to cry about you may use this:
where a.Id = case when b.field7 LIKE '%[^0-9]%' THEN NULL ELSE CONVERT (int, b.field7) END
_____________
Code for TallyGenerator -
Thanks for your idea but i think this is better because that way i dont destroy the index of table what do you think abaut??
SELECT
a.Desc
FROM TableA
INNER JOIN TableB ON A.ID = CASE
WHEN ISNUMERIC(B.field7) 1 THEN 0
ELSE b.field7
END
-
Sorry to disappoint you but you do destroy the index.
Check the execution plan. You'll see both CONVERT and clustered index scan (which is the same as table scan) there.
_____________
Code for TallyGenerator -
Sorry but I did not read your original post too carefully and did not realize your major objective was to make sure the index is used during your query.
I kind of assumed this is one time deal of reloading some data not production database issue.
The problem you are facing is not the usage of the index or not but incorrect design of your database forcing you to use some strange tricks to get your work done.
In most cases (sometimes you stuck with something you can not control) fixing the design is the most efficient solution to many SQL issues.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply