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
 General SQL Server Forums
 New to SQL Server Programming
 Oddity in a select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  06:04:48  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/17/2013 :  06:06:58  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  06:10:23  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  06:13:15  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  06:20:36  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  06:22:41  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  06:35:59  Show Profile  Reply with Quote
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).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  06:37:41  Show Profile  Reply with Quote
Yep...so my guess was right. Can you show that CASE statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  06:39:05  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/17/2013 :  06:40:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  06:41:48  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  06:52:00  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  06:54:34  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  06:55:52  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  06:56:50  Show Profile  Reply with Quote
Thanks and will do.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  06:57:52  Show Profile  Reply with Quote
quote:
Originally posted by rmg1

Thanks and will do.


you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 06/17/2013 :  10:37:34  Show Profile  Reply with Quote
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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/17/2013 :  11:06:15  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 06/17/2013 11:06:39
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/17/2013 :  11:54:53  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 06/17/2013 :  13:06:29  Show Profile  Reply with Quote
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
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.11 seconds. Powered By: Snitz Forums 2000