Author |
Topic |
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 06:04:48
|
Hi allI've got a select statement that runs off a view.This is quite normal, but here's the odd bit.If I run the select statement in SSMS I get an error.I comment out the offending line and the query runs as normal.If I remove the comment markers from the offending line, the query then runs and brings back the extra field.I'm completely at a loss as to why. Anyone any ideas???(Apologies but I can't post the code (orders from above). I know answers will be a bit vague because of this.) |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:06:58
|
I dont think anybody would be able to guess the issue without seeing code or the offending line!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 06:10:23
|
Justy had the OK to post the select statement and here it is:-set dateformat dmydeclare @startdate datetimedeclare @enddate datetimedeclare @customer varcharset @startdate='20/08/2012'set @enddate='31/08/2012'set @customer='Cemex UK'SELECT top 7 [AssessmentID] ,[Assessment Date] ,[ClientID] ,[Customer Name] ,[EmployeeID] ,[Employee Name] ,[DateOfBirth] ,[Age] ,[Age Range] ,[Gender] ,[Job Title] ,[Division] ,[Location] ,[BMI Status] ,[Musculoskeletal Results] ,[Musculoskeletal Pie] ,[Neck] ,[Shoulders] ,[Arm(s)] ,[Wrist/Hand/Fingers] ,[Upper Spine] ,[Lower (lumbar) spine] ,[Leg (including hip/knee)] ,[Ankle/foot] ,[Asthma] ,[TakingMedication] ,[HistoryHypertension] ,[HistoryEpilepsy] ,[HistoryDiabetes] ,[HistoryRespiratory] ,[Respiratory Symptoms] ,[Respiratory Questions] ,[Respiratory Result] ,[Smoker] ,[Ex Smoker] ,[Lung Function Test] ,[Lung Function Result] ,[OverDrinking] ,[BP Test] ,[BP Status] ,[Aware of BP Issue] ,[Uncontrolled Diabetes] ,[Assessment Type] ,[Outcome] ,[Consent] ,[Previous Hearing Category] ,[Current Hearing Category] ,[Previous Hearing Score] ,[Current Hearing Score] ,[Hearing Rating] ,[Audiometric Result] ,[Audiometric Score] ,[Skin Result] --,[Skin Score] --,[Distance Vision Test] --,[Distance Vision Result] --,[Near Vision Test] --,[Near Vision Result] --,[Distance & Near Vision Result] --,[Refer to Optician] --,[Monocular Vision Test] --,[Monocular Vision Result] --,[Colour Vision Test] --,[Colour Vision Result]FROM [MI_Development].[dbo].[vw_GlobalAnalysisReport]WHERE ([Assessment Date] BETWEEN @StartDate AND @EndDate) --AND ([Customer Name] = '%cemex%')order by [AssessmentID] The line for [Skin Result] (amongst others) was causing issues until I commented it our, reran the select, uncommented it and ran the select again. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:13:15
|
whats the definition for ,[Skin Result] in the view [MI_Development].[dbo].[vw_GlobalAnalysisReport]? is it coming straight from a table or is it a calculated column?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 06:20:36
|
It's the result of a CASE statement.But like I said, if I comment it out and run the query, it works.If I then uncomment it, it works and pulls back the right fields and the right values. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:22:41
|
may the datatype of various fields used in CASE WHEN was not compatible or there may be some spurious data in one of the involved fields which might have caused some conversion errors.Is the above query part of some update statement? ALso can you post what error you initially got?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 06:35:59
|
This is the error I got initially:-Msg 8114, Level 16, State 5, Line 9Error converting data type varchar to float.Skin Result in the view is listed as a varchar(50).Skin Result in the base table is also a varchar(50).As far as I can see, there's no conversions being done (even implicit ones). |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:37:41
|
Yep...so my guess was right. Can you show that CASE statement?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 06:39:05
|
This is the CASE statement:- ,[Skin Result] = CASE WHEN T.[SkinResult] IS NULL THEN 'Not Tested' ELSE T.[SkinResult] ENDIt's varchar in all cases so I don't get the reference to float in the error message. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-17 : 06:40:14
|
Post us the CASE statement too....You might have this kind of problemCASE WHEN condition1 THEN ColumnName1ofFloat WHEN condition2 THEN ColumnName2ofFloat ELSE SomeColumnofVarcharEND --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:41:48
|
ok...is there any other columns in the list which contains a case expression and involves columns of string based types(varchar,char etc) and float type?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:52:00
|
or it can be any of fields like [Skin Score] etc which may have a float (or numeric) value and you might have used it just like above in a case statement. This would cause conversion error as the default value 'Not Tested' cant be converted to numeric type.As a matter of fact all values returned by various conditions of CASE..WHEN should be of similar (mutually compatible) types with no incompatible data in them.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 06:54:34
|
There's quite a few case statements in the view code.Looks like I'll have to go through them all and double-check what the field-types are and what the output is.Thanks for pointing me in the right direction. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:55:52
|
yep..that should help you to get this sortedLet me know if you need more assistance on any of them.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 06:56:50
|
Thanks and will do. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:57:52
|
quote: Originally posted by rmg1 Thanks and will do.
you're welcome ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-06-17 : 10:37:34
|
Unfortunately, I've had to come back.I've sorted most of the issues but this one's got me stumped!!!I've got a subquery that looks like this:-SELECT DISTINCT [Customer Name] = (SELECT C.[ClientName] FROM [Common].[dbo].[Client] C WHERE E.[ClientID] = C.[ClientID]) ,R.[AssessmentID] ,[QuestionID] ,[Question] ,[BOO1] ,[BOO2] ,[BOO3] ,CASE WHEN ISNUMERIC([MoreDetail]) = 1 THEN [MoreDetail] ELSE NULL END AS [MoreDetail] ,E.[EmpGender]--,A.[GlucoseRange] ,T.[Glucose] ,T.[LungFVCResult]FROM [NUOHHealthScreening].[dbo].[tblResponses] R INNER JOIN [NUOHHealthScreening].[dbo].[tblAssessmentMedicalTests] T ON R.AssessmentID = T.AssessmentID INNER JOIN [NUOHHealthScreening].[dbo].[tblAssessmentEmpDetails] E ON R.AssessmentID = E.AssessmentIDwhere [QuestionID]=78order by R.[AssessmentID] and that works for any date-trange you'd care to mention.However, when the subquery goes through this case statement:- ,[Over] = CASE WHEN SUM(CASE WHEN [QuestionID] = 78 AND [BOO2] = 1 AND LEFT([EmpGender],1) = 'F' AND CONVERT(float,[MoreDetail]) >= 15 THEN 1 WHEN [QuestionID] = 78 AND [BOO2] = 1 AND LEFT([EmpGender],1) = 'M' AND CONVERT(float,[MoreDetail]) >= 22 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END it throws up the error about converting varchar to float.As far as I can see looking through the subquery output, there's nothing left in there that could be a varchar, they have to be numeric.I'd be grateful for any further assistance. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-17 : 11:06:15
|
I think MoreDetail of varchar datatype? declare @tab table (col1 varchar(100),col2 int)insert into @tab values ('x',1),('10',1),('15',1),('20',1),('25',1)select case when Convert(float,col1)>=15 then 1 when Convert(float,col1)>=22 then 1 Else 0 ENDfrom @tabCheersMIK |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 11:54:53
|
check if MoreDetail have any spurious data which cant be converted to valid float type.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-17 : 13:06:29
|
Run this query to help you figure out which rows may be causing the problem. It is not a perfect test, but should help you some:SELECT * FROM TheTableThatHasMoreDetailColumn WHERE ISNUMERIC(MoreDetail) = 0 |
|
|
|