case in 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

SQL Server 2005 Forums

Transact-SQL (2005)

case in where clause

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-02-29 : 07:57:28

hello,

Can anybody see why this is failing at line 19
incorrect syntax near =
?

declare @Date datetime
declare @type int
declare @isnew int
declare @isreturn int
declare @isold int
SET @Date = '2008-03-04'
SET @type = 1
SET @isnew=1

SELECT [date],
SUM(amount) as s_amount
FROM values
WHERE convert(char(10),[date],23) = @Date
AND status > 0 AND

CASE WHEN @isnew=1 THEN
(loan.isnew=1)
WHEN @isreturn=1 THEN
((loan.isreturn=1) and loan.isold=0)
WHEN @isold=1 THEN
loan.isold=1
END
AND type = @type
GROUP BY [date]

kind regards,
jamie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 07:59:59

The error is because you are trying to make assignment in the WHERE clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-29 : 08:06:52

double check my logic coz i'm having a bad day but is this the same thing...

where
....
and
(
(@isnew=1 and loan.isnew=1)
or
(@isreturn=1 and loan.isreturn=1 and loan.isold=0)
or
(@isold=1 and loan.isold=1)
)

Em

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-02-29 : 08:30:27

hi,
what I'm trying to do is if @isnew=1 then only add the @isnew=1 part.

If @isold=1 then only use the @isold=1 part..

(when @isnew=1 the other @is fields can be 1 or 0

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-02-29 : 08:35:11

you know what , I think your code works Em !

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-29 : 08:39:06

lol, maybe not such a bad day after all