where clause issue - 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)

where clause issue

Author

Topic

cardgunner

326 Posts

Posted - 2011-12-23 : 09:28:01

I have an isue with the following where cluase


declare @paid int
set @paid=1

select *
from tmp
where case when @paid=1 then '' else (cast(t_balc as decimal (9,2))!=0) end

It errors saying "Incorrect syntax near '!'"

it does work when the where clause is

where (cast(t_balc as decimal (9,2))!=0)

CardGunner

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-23 : 09:31:10

You can't have an logical operator in the then or else portions of a CASE statement. It has to be just a value (literal, column or expression)

What can be done is something like this

WHERE (case when @paid=1 then '' else cast(t_balc as decimal (9,2)) end) != 0

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-23 : 09:52:19

well you can't mix and match a literal and a number either

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-23 : 10:14:29

Sure you can. SQL will cast string to int and the '' will become 0.

DECLARE @test INT = 1;

SELECT * FROM sys.tables AS t 
	WHERE CASE WHEN @test = 1 THEN '' ELSE object_id END != 0

Returns no rows because the '' is cast to an int becoming 0. So that expression reduces to WHERE 0 != 0, which is false for all rows. Change the variable to something else and you get all rows.

--
Gail Shaw
SQL Server MVP

cardgunner

326 Posts

Posted - 2011-12-23 : 10:15:54

where cast((case when @paid=1 then '' else facr200.t_balc end) as decimal(9,2)) != 0

Looks like it might work. Need to test the results.

Thanks and Merry Christmas.

CardGunner

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-23 : 10:17:19

Would you say it was a good practice..it's just another hole in the wall

So why not just say 0...I'll tell you why, because it should be null, developers in sql server don't think that way, and it's just plain wrong

Work on DB2 Mainframe

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-23 : 10:24:18

No, it's not good practice, but it does work.

--
Gail Shaw
SQL Server MVP

cardgunner

326 Posts

Posted - 2011-12-23 : 10:26:59

Correction it is not returning all records when variablle is 1

CardGunner

cardgunner

326 Posts

Posted - 2011-12-23 : 10:33:21

Hold on i got messed up. This is not working.

I want it to show all records if the variable is 1. If the variable is anything other then 1 show the records where t_balc is not 0.

CardGunner

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-23 : 11:01:18

you do know that this make NO sense whatsoever

WHAT Does a local variable have to do ANYTHING with the data in a table?

IF @paid <> 1
SELECT * FROM tmp
WHERE t_balc <> 0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

cardgunner

326 Posts

Posted - 2011-12-23 : 11:09:11

Well I'm using the variable as a toggle of sorts. I want to see the records where there is a balance then see all records. Depending on which, i would set the variable.

Ultimately I'm taking this and making a report.

Foe example I have to see what still needs to be paid. I can run query. Now I want to see what was paid and what is current and what was wrote off. I would like to run this from the same report using the same query. I was told early on to do as much work as you can in the sql statement vs relying on reporting filters.

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 11:13:59

quote:
Originally posted by cardgunner

Hold on i got messed up. This is not working.

I want it to show all records if the variable is 1. If the variable is anything other then 1 show the records where t_balc is not 0.

CardGunner

that will translate in sql to


SELECT * FROM tmp 
WHERE (t_balc <> 0
OR @variable = 1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-23 : 12:19:40

he needs a union I think

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-23 : 12:29:38

No, Visakhm's query will do the job. I do hope that performance is not a requirement for this query though. This is one of those patterns that devs love because it looks so elegant and DBAs hate because it performs shockingly bad on larger rowcounts.

--
Gail Shaw
SQL Server MVP

cardgunner

326 Posts

Posted - 2011-12-23 : 12:40:54

It worked wonderful. And it performs good. At least I'm not waiting for the query to run.

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 13:08:06

quote:
Originally posted by cardgunner

It worked wonderful. And it performs good. At least I'm not waiting for the query to run.

CardGunner

its good solution until results are large as Gail points out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-23 : 13:30:26

Just to clarify, I'm not waving around theoretical problems that have no impact on reality. I clean that code pattern out of clients' code all the time

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources