October 4, 2016 at 7:10 pm
I know this is a beginner type question but I am stumped.
I am attempting to convert a table called 'Inactive' which is a Boolean table. Its referenced from a Dept_ID #:
UPDATE Inventory
SET Inactive = 1
WHERE Dept_ID IN(1081, 1091, 1092, 1093, 1094, 1101, 1102, 1103, 1104, 1105, 1111, 1112, 1113)
But I am getting this:
Conversion failed when converting the nvarchar value 'NONE' to data type int.
There are a few cells with NONE in them.
Thanks.
October 4, 2016 at 10:05 pm
chef423 (10/4/2016)
I know this is a beginner type question but I am stumped.I am attempting to convert a table called 'Inactive' which is a Boolean table. Its referenced from a Dept_ID #:
UPDATE Inventory
SET Inactive = 1
WHERE Dept_ID IN(1081, 1091, 1092, 1093, 1094, 1101, 1102, 1103, 1104, 1105, 1111, 1112, 1113)
But I am getting this:
Conversion failed when converting the nvarchar value 'NONE' to data type int.
There are a few cells with NONE in them.
Thanks.
From the issue description I may conclude that the data type of Dept_ID is nvarchar.
In this case you should compare it to values of the same data type:
UPDATE Inventory
SET Inactive = 1
WHERE Dept_ID IN(N'1081', N'1091', N'1092', N'1093', N'1094', N'1101', N'1102', N'1103', N'1104', N'1105', N'1111', N'1112', N'1113')
_____________
Code for TallyGenerator
October 4, 2016 at 10:13 pm
Thanks. Worked fine. Kudos!
October 13, 2016 at 2:40 pm
In case you wanted to know why Sergiy's answer is correct, your IN clause listed integer values (no quotes), even though the column is defined as a character string. This tells SQL to treat them as integers for the comparison and implicitly converts the Dept_ID to an integer. The error occurred when SQL parsed the text value 'NONE', which obviously cannot be changed to an integer.
By wrapping the integer values in single quotes, as Sergiy did, you're instructing SQL to treat Dept_ID as a string.
The parsing error will only occur if the record to be considered cannot be parsed. If you eliminate the records another way, the record would not be considered and the implicit conversion to integer would succeed.
DECLARE @T TABLE ( c CHAR(2) );
INSERT INTO @T ( c )
VALUES ( 'A' ),( 'B' ),( 'C' );
INSERT INTO @T ( c )
VALUES ( 1 ),( 2 ),( 10 ),( 20 );
SELECT * FROM @T AS t
WHERE t.c IN ( 1, 2, 10 )
AND ISNUMERIC(c) = 1--Removing this results in a parsing error
AND t.c < 3--10 < 3 = false
ORDER BY t.c;
SELECT * FROM @T AS t
WHERE t.c IN ( 1, 2, 10 )
AND ISNUMERIC(c) = 1--Removing this results in a parsing error
AND t.c < '3'-- '10' < '3' = true
ORDER BY t.c;
For simple equality checks, the implicit cast isn't much of a problem. It will either run or fail. However implicit casts with other comparisons can affect your results as demonstrated above.
Wes
(A solid design is always preferable to a creative workaround)
October 13, 2016 at 3:03 pm
whenriksen (10/13/2016)
...SELECT * FROM @T AS t
WHERE t.c IN ( 1, 2, 10 )
AND ISNUMERIC(c) = 1--Removing this results in a parsing error
AND t.c < 3--10 < 3 = false
ORDER BY t.c;
...
This is not really good example.
If on a reasonably big table statistics suggest that the condition "t.c < 3" will be significantly more selective that "ISNUMERIC(c) = 1" (which would be considered 50% selective because the use of the function) then the engine will apply the last condition first, before ISNUMERIC, causing conversion error.
Also, ISNUMERIC is not good for validating integers.
ISNUMERIC ('.') = 1, but WHERE '.' < 1 causes run-time error.
To be safe your WHERE clause must look like this:
SELECT * FROM @T AS t
WHERE t.c IN ( '1', '2', '10' )
AND CASE WHEN t.c like '%[^0-9]% -- this checks if any character within the string is not a digit
THEN NULL ELSE t.c END < 3
ORDER BY t.c;
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply