|
vqcheese
Starting Member
USA
19 Posts |
 Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/30/2012 : 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 |
 |
|
Error Converting data type
