SQL 2000 Using "Case" statement with "in" clause 1

[RileyCat]

I'm trying to write a "case" statement with the "in" clause and am getting compile errors.

So here's my code . . .

where . . .
      IdCapPlusSrc in
	case
		when @Status = 4004 then
			(select distinct IdCapPlusSrc
			 from   CapPlus with (nolock)
			 where	TmsSent is NULL)
		else (select IdCapPlusSrc
                      from   CapPlusSrc with (nolock)
                      where  Status = @Status)
		end

The errors I'm getting on compile . . .

Incorrect syntax near the keyword 'case'.
Incorrect syntax near the keyword 'else'.

So is the "in" clause not allowed with a "case" statement, or do I have some bad syntax?

Any help is appreciated.

Thanks!

Stay Cool Ya'll!

-- Kristin

 

[bborissov]

where . . .
     ((@Status = 4004 AND  
       IdCapPlusSrc in (select distinct IdCapPlusSrc
                               from   CapPlus with (nolock)
                        where    TmsSent is NULL)) OR
      (@Status <> 4004 AND  
       IdCapPlusSrc in (select IdCapPlusSrc
                               from   CapPlusSrc with (nolock)
                        where  Status = @Status)))

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RileyCat]

DOH!

Thank you!

Stay Cool Ya'll!

-- Kristin