Having/Where Clause - 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

Having/Where Clause

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-16 : 08:16:44

Why is my where clause not accepting IN but like = only

SELECT TOP 100 PERCENT dbo.tPriorityCode.PriorityCode, COUNT(dbo.tCalls.CallID) AS CallID, COUNT(dbo.tCalls.CallID) AS CountCallID
FROM dbo.tCalls INNER JOIN
dbo.tPriorityCode ON dbo.tCalls.PriorityCodeID = dbo.tPriorityCode.PriorityCodeID
WHERE (dbo.tCalls.CallStatusID IN '1,2,3')
GROUP BY dbo.tPriorityCode.PriorityCode

If it is that easy, everybody will be doing it

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-16 : 08:33:55

[code]
if CallStatusID is string then it should be

WHERE dbo.tCalls.CallStatusID IN ('1','2','3')

or else

WHERE dbo.tCalls.CallStatusID IN (1,2,3)

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 08:35:03

IN keyword needs a list of options embedded with paranthesis.

IN (p1, p2, p3, ...)

Very easy to find in Books Online. Just highlight the IN keyword and press SHIFT-F1.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 08:39:09

Also,

1) Why TOP 100 PERCENT? Are you using the query in a VIEW?
2) Why are you counting dbo.tCalls.CallID twice?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 08:47:08

Third. Why are you JOINING the tPriorityCode table?
Since you are binding the tables over PriorityCode columns, this will run much faster!

SELECT		PriorityCode,
		COUNT(CallID) AS CallID,
		COUNT(CallID) AS CountCallID
FROM		dbo.tCalls
WHERE		CallStatusID IN ('1,2,3')
GROUP BY	PriorityCode

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources