SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error Converting data type
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vqcheese
Starting Member

USA
19 Posts

Posted - 11/30/2012 :  09:20:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/30/2012 :  09:38:10  Show Profile  Reply with Quote
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
Go to Top of Page

vqcheese
Starting Member

USA
19 Posts

Posted - 11/30/2012 :  09:50:01  Show Profile  Reply with Quote
using Null instead of '' Worked
Thanks.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/30/2012 :  10:14:42  Show Profile  Reply with Quote
You're welcome.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000