Error Converting data type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error Converting data type

Author

Topic

vqcheese
Starting Member

19 Posts

Posted - 2012-11-30 : 09:20:20

i get this error when i execute the following query:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

--Here is my query
SELECT Companynumber, CustomerSgt, Itemsgt, LabTestSgt, MinimumValue, TargetValue, MaximumValue,
MAX(CASE WHEN TestNumber = 1 THEN itemsgt ELSE '' END) 'LabTestOne',
MAX(CASE WHEN TestNumber = 2 THEN itemsgt ELSE '' END) 'LabTestTwo',
MAX(CASE WHEN TestNumber = 3 THEN itemsgt ELSE '' END) 'LabTestThree',
MAX(CASE WHEN TestNumber = 4 THEN itemsgt ELSE '' END) 'LabTestFour',
MAX(CASE WHEN TestNumber = 5 THEN itemsgt ELSE '' END) 'LabTestFive',
MAX(CASE WHEN TestNumber = 6 THEN itemsgt ELSE '' END) 'LabTestSix',
MAX(CASE WHEN TestNumber = 7 THEN itemsgt ELSE '' END) 'LabTestSeven',
MAX(CASE WHEN TestNumber = 8 THEN itemsgt ELSE '' END) 'LabTestEight',
MAX(CASE WHEN TestNumber = 9 THEN itemsgt ELSE '' END) 'LabTestNine',
MAX(CASE WHEN TestNumber = 10 THEN itemsgt ELSE '' END) 'LabTestTen'
FROM
(
SELECT CompanyNumber, CustomerSgt, Itemsgt, LabTestSgt, MinimumValue, TargetValue, MaximumValue,
ROW_NUMBER() OVER(Partition BY CompanyNumber, customersgt, itemsgt order by labtestsgt) 'TestNumber'
FROM NOFCustomerTestLimits

) ts
where itemsgt = '40'
GROUP BY Companynumber, CustomerSgt, Itemsgt, LabTestSgt, MinimumValue, TargetValue, MaximumValue

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-30 : 09:38:10

If itemsgt is an integer then your case staement is returning 2 different data types -- an integer for itemsgt
and a string '', which SQL can't resolve

try MAX(CASE WHEN TestNumber = 1 THEN itemsgt ELSE NULL END) 'LabTestOne',

Jim

Everyday I learn something that somebody else already knew

vqcheese
Starting Member

19 Posts

Posted - 2012-11-30 : 09:50:01

using Null instead of '' Worked
Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-30 : 10:14:42

You're welcome.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources