count

Hi
Can someone tell me what I am doing wrong here? I am not sure I am using
count in the right context.
Thanks.
declare @.ServiceID int
declare @.WorkTypeID int
declare @.PriorityID int
declare @.GeogID int
if count
Select
CA.ContractID,
C.Contract,
CA.CoverCodeID,C.AgentID
FROM
ContractService CA
INNER JOIN Contract C ON CA.ContractID = C.ContractID
INNER JOIN ContractServiceSchedule CS on CS.ContractServiceID =
CA.ContractServiceID
WHERE (CA.ServiceID = @.ServiceID) and (C.ContractStatus = 18002)
AND CS.WorkTypeID = @.WorkTypeID AND CS.PriorityID = @.PriorityID
AND Datediff(d,CS.StartDate,Getdate()) >= 0
AND Datediff(d,CS.StopDate,Getdate()) <= 0
AND CA.GeographyID = @.GeogID) > 0I am not sure that you posted the right statement, there is no count around
?!
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jaco Wessels" <jaco_wess@.yahoo.co.uk> schrieb im Newsbeitrag
news:ewDNNG$QFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi
> Can someone tell me what I am doing wrong here? I am not sure I am using
> count in the right context.
> Thanks.
> declare @.ServiceID int
> declare @.WorkTypeID int
> declare @.PriorityID int
> declare @.GeogID int
> if count
> Select
> CA.ContractID,
> C.Contract,
> CA.CoverCodeID,C.AgentID
> FROM
> ContractService CA
> INNER JOIN Contract C ON CA.ContractID = C.ContractID
> INNER JOIN ContractServiceSchedule CS on CS.ContractServiceID =
> CA.ContractServiceID
> WHERE (CA.ServiceID = @.ServiceID) and (C.ContractStatus = 18002)
> AND CS.WorkTypeID = @.WorkTypeID AND CS.PriorityID = @.PriorityID
> AND Datediff(d,CS.StartDate,Getdate()) >= 0
> AND Datediff(d,CS.StopDate,Getdate()) <= 0
> AND CA.GeographyID = @.GeogID) > 0
>|||If all you care about is whether rows exist or not in the result of the
query, use the EXIST predicate as it will stop processing the query as soon
as the answer is known to be TRUE or FALSE:
IF EXISTS(SELECT...)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Jaco Wessels" <jaco_wess@.yahoo.co.uk> wrote in message
news:ewDNNG$QFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi
> Can someone tell me what I am doing wrong here? I am not sure I am using
> count in the right context.
> Thanks.
> declare @.ServiceID int
> declare @.WorkTypeID int
> declare @.PriorityID int
> declare @.GeogID int
> if count
> Select
> CA.ContractID,
> C.Contract,
> CA.CoverCodeID,C.AgentID
> FROM
> ContractService CA
> INNER JOIN Contract C ON CA.ContractID = C.ContractID
> INNER JOIN ContractServiceSchedule CS on CS.ContractServiceID =
> CA.ContractServiceID
> WHERE (CA.ServiceID = @.ServiceID) and (C.ContractStatus = 18002)
> AND CS.WorkTypeID = @.WorkTypeID AND CS.PriorityID = @.PriorityID
> AND Datediff(d,CS.StartDate,Getdate()) >= 0
> AND Datediff(d,CS.StopDate,Getdate()) <= 0
> AND CA.GeographyID = @.GeogID) > 0
>|||On Mon, 18 Apr 2005 09:36:26 +0100, Jaco Wessels wrote:

>Hi
>Can someone tell me what I am doing wrong here? I am not sure I am using
>count in the right context.
Hi Jaco,
Itzik is right: EXISTS is better in this case, since it allows SQL
Server to stop searching for more rows as soon as the first is found.
However, for future use, I'll point out what your error was. Your query
was (simplified):
IF COUNT
SELECT bla bla bla
FROM bla bla bla
WHERE bla bla bla > 0
And the correct syntax is
IF (SELECT COUNT(*)
FROM bla bla bla
WHERE bla bla bla) > 0
Note the placement of the COUNT function _IN_ the select list, as a
replacement for what you have there (yoou don't care about the columns -
if there's a row, it counts, whatever the columns' contents). Also note
the use of parentheses around the complete query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)