Oddity in a select statement - 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

General SQL Server Forums

New to SQL Server Programming

Oddity in a select statement

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-06-17 : 06:04:48

Hi all

I'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 MVP
http://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 dmy
declare @startdate datetime
declare @enddate datetime
declare @customer varchar

set @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 MVP
http://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 MVP
http://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 9
Error 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 MVP
http://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] END

It'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 problem


CASE WHEN condition1 THEN ColumnName1ofFloat
     WHEN condition2 THEN ColumnName2ofFloat
     ELSE SomeColumnofVarchar
END

--
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 MVP
http://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 MVP
http://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 sorted

Let me know if you need more assistance on any of them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://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.AssessmentID
where
	[QuestionID]=78
order 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
END
from @tab

Cheers
MIK

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 MVP
http://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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources