0

The following query should return records that consist of 3 or 4 digits that are converted from nvarchar to integer and are lower than 1200. Instead of a desired result, an error returns stating: Conversion failed when converting the nvarchar value '' to data type int.

UPDATE cvUpd
SET cvUpd.visible = case when CONVERT(int, cvFrom.ConfigText) < 1200 then 0 else 1 end
FROM ConfigValue cvUpd
INNER JOIN OptionRestriction r ON cvUpd.ConfigurationID=67061
   AND cvUpd.FeatureID = r.Feature_ID_2
   AND cvUpd.OptionValue= r.OptionValue_2
INNER JOIN ConfigValue cvFrom ON cvFrom.FeatureID =r.Feature_ID_1  
   AND cvFrom.ConfigurationID = cvUpd.ConfigurationID 
   AND cvFrom.OptionValue=r.OptionValue_1 
   AND cvFrom.ConfigText=r.value
   AND cvFrom.ConfigText NOT LIKE '%[^0-9]%' 
   AND (LEN(cvFrom.ConfigText) = 3 or LEN(cvFrom.ConfigText) = 4)

I also tried to run a select query, but this query resulted in the same error:

SELECT CONVERT(int, cvFrom.ConfigText)
FROM ConfigValue cvUpd
INNER JOIN OptionRestriction r ON cvUpd.ConfigurationID=67061
   AND cvUpd.FeatureID = r.Feature_ID_2
   AND cvUpd.OptionValue= r.OptionValue_2
INNER JOIN ConfigValue cvFrom ON cvFrom.FeatureID =r.Feature_ID_1  
   AND cvFrom.ConfigurationID = cvUpd.ConfigurationID 
   AND cvFrom.OptionValue=r.OptionValue_1 
   AND cvFrom.ConfigText=r.value
   AND cvFrom.ConfigText NOT LIKE '%[^0-9]%' 
   AND (LEN(cvFrom.ConfigText) = 3 or LEN(cvFrom.ConfigText) = 4)

UPDATE

It appears that I get the same error even without using convert:

SELECT cvFrom.ConfigText
FROM ConfigValue cvUpd
INNER JOIN OptionRestriction r ON cvUpd.ConfigurationID=67061
   AND cvUpd.FeatureID = r.Feature_ID_2
   AND cvUpd.OptionValue= r.OptionValue_2
INNER JOIN ConfigValue cvFrom ON cvFrom.FeatureID =r.Feature_ID_1  
   AND cvFrom.ConfigurationID = cvUpd.ConfigurationID 
   AND cvFrom.OptionValue=r.OptionValue_1 
   AND cvFrom.ConfigText=r.value
   AND cvFrom.ConfigText NOT LIKE '%[^0-9]%' 
   AND (LEN(cvFrom.ConfigText) = 3 or LEN(cvFrom.ConfigText) = 4)
user2237168
  • 305
  • 1
  • 3
  • 17
  • your select is not same as your join,can you run the total query in your join – TheGameiswar Sep 11 '17 at 09:11
  • `CONVERT(nvarchar(MAX),CONVERT(int, cvFrom.ConfigText)) < 1200`: why on earth do you convert to `nvarchar` if all you do with it is compare to an `int`?? – HoneyBadger Sep 11 '17 at 09:11
  • I read on a website that this could be a fix when a case statement is involved. I also tried the normal CONVERT(int, cvFrom.ConfigText)) < 1200, so without CONVERT (nvarchar(max), ... ) but that did not work either. – user2237168 Sep 11 '17 at 09:15
  • @TheGameiswar you are right. If I execute a similar select query than I get the same error. The case is thus not the problem. – user2237168 Sep 11 '17 at 09:41
  • use try_Convert, you could see nulls for the ones that might have caused the issue..This works if you are on sql2012 – TheGameiswar Sep 11 '17 at 09:43
  • @TheGameiswar unfortunately, try_Convert does not work. Do you have any other ideas? – user2237168 Sep 11 '17 at 09:52

1 Answers1

0

The problem was that r.value was an integer that was joined with the nvarchar "cvFrom.ConfigText". I therefore needed to edit AND cvFrom.ConfigText=r.value to AND cvFrom.ConfigText=CONVERT(nvarchar(max), r.value).

user2237168
  • 305
  • 1
  • 3
  • 17