Weird Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Weird Query

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-11-30 : 22:20:57

Hi there

I have a query that is working for the last few months:

select  qst.ParentQuestionID, qst.questioncode, qst.QuestionName, qst.SurveyID, ans.UserID,
		case
		when substring(qst.questioncode,1,2) in (select substring(q.questioncode, 4,2) 
													from questions q
															join answers ans1 on ans1.questionid = q.questionid
													where q.isActive = 1 and q.questioncode like '00%' 
															and q.surveyid = 30 and ans1.Answer = '0' and ans1.UserID = ans.UserID) then 0
		else 
			qo.QuestionOptionDesc end as QuestionOptionDesc

from questions qst
		left join Answers ans on ans.QuestionID = qst.QuestionID
		left join questionoptions qo on qo.QuestionOptionID = ans.Answer
		left join Users us on us.UserID = ans.UserID
where qst.parentQuestionID in (select questionID from questions where hasmatrix = 1) 
		and us.ClientID = 25 and qst.SurveyID = 30 
order by us.Firstname, qst.DisplayOrder

Now I get error saying that Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

So I trace this and I couldn't work it this and till I accidently putting additional filter "and us.UserID IN (SELECT UserID FROM Users WHERE ClientID = 25)" so become like this:

select  qst.ParentQuestionID, qst.questioncode, qst.QuestionName, qst.SurveyID, ans.UserID,
		case
		when substring(qst.questioncode,1,2) in (select substring(q.questioncode, 4,2) 
													from questions q
															join answers ans1 on ans1.questionid = q.questionid
													where q.isActive = 1 and q.questioncode like '00%' 
															and q.surveyid = 30 and ans1.Answer = '0' and ans1.UserID = ans.UserID) then 0
		else 
			qo.QuestionOptionDesc end as QuestionOptionDesc

from questions qst
		left join Answers ans on ans.QuestionID = qst.QuestionID
		left join questionoptions qo on qo.QuestionOptionID = ans.Answer
		left join Users us on us.UserID = ans.UserID
where qst.parentQuestionID in (select questionID from questions where hasmatrix = 1) 
		and us.ClientID = 25 and qst.SurveyID = 30 and us.UserID IN (SELECT UserID FROM Users WHERE ClientID = 25)
order by us.Firstname, qst.DisplayOrder

Can someone explain about this?!?! Cause the us.ClientID = 25 is actually similar to us.UserID IN (SELECT UserID FROM Users WHERE ClientID = 25) in this context ?!!?!

I know it's working now BUT why ?!?! Why suddenly today the previous query is not working ?!?!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources