case statement in a where clause

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 2000 Forums

SQL Server Development (2000)

case statement in a where clause

Author

Topic

cardgunner

326 Posts

Posted - 2008-09-17 : 09:18:51

How do I exclude the records in my query where t_lbal<>0 and @owed='yes' but if @owed='no' then don't exlude any records.

Sounds simple enough but I must be having a Senior moment on how to do it becuse I can't.

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 09:41:23

WHERE (t_lbal<>0 and @owed='yes') or @owed='no'

cardgunner

326 Posts

Posted - 2008-09-17 : 10:01:05

Thanks for the reply but it didn't work as written.

here is some sample code. I should have supplied this before.


declare @owed varchar
select @owed='yes'

select *
from
(select '1' as id, 200.00 as t_tamt, 0.00 as t_lbal union all
select 2,250, 250 union all
select 3,300, -100 union all
select 4,350, 0 union all
select 5,400,400
) tmp

Now if @owed is 'yes' then I want to exclude all the records where there is a 0 in the t_lbal field.

However if @owed is 'no' then show all the records

I got 0 results with
WHERE (t_lbal<>0 and @owed='yes') or @owed='no'

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 10:29:38

quote:
Originally posted by cardgunner

Thanks for the reply but it didn't work as written.

here is some sample code. I should have supplied this before.

declare @owed varchar(3)
select @owed='yes'

select *
from
(select '1' as id, 200.00 as t_tamt, 0.00 as t_lbal union all
select 2,250, 250 union all
select 3,300, -100 union all
select 4,350, 0 union all
select 5,400,400
) tmp

Now if @owed is 'yes' then I want to exclude all the records where there is a 0 in the t_lbal field.

However if @owed is 'no' then show all the records

I got 0 results with
WHERE (t_lbal<>0 and @owed='yes') or @owed='no'

CardGunner

the problem is not in query given. you havent specified a length for varchar so it will take default length (usually 1). thats why value gets truncated to y and where wont work. change like above and see

cardgunner

326 Posts

Posted - 2008-09-17 : 10:34:41

You are right!

But I think you knew that.

Thanks so much.

CardGunner

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-18 : 06:17:42

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources