What is wrong with my statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

What is wrong with my statement?

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-25 : 21:55:58

I actualy using SQL server 2005. I'm a stored procedure and it's accusing error near '=' that is in case clause. Do you know in what I'm wrong?

Select u.nick as 'user', l.nick as 'last_user', t.*
from threads t
inner join users u on t.user_id = u.user_id
inner join users l on t.last_user_id = l.user_id
where t.creation > @Period and forum_id=(@forum_id) and
case when @PageType = 'votacao' then t.votacao = 1 end,
case when @PageType = 'topicos' then t.votacao = 0 end,
case when @PageType = 'todos' then end

Thank you very much in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 21:58:47

What sure what is your logic with the case when there. See if this is what you want ?


Select u.nick as 'user', l.nick as 'last_user', t.*
from   threads t
inner join users u on t.user_id = u.user_id
inner join users l on t.last_user_id = l.user_id
where t.creation > @Period 
and   forum_id = (@forum_id) 
and   t.votacao = case when @PageType = 'votacao' then 1
                       when @PageType = 'topicos' then 0
                       end

KH

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-25 : 22:17:03

The clause case woks fine now, but that's not returning any value, which should. And I have to put in case could be both Here are the all code:

declare @PageType varchar

set @PageType = 'votacao'

Select u.nick as 'user', l.nick as 'last_user', t.*
from threads t
inner join users u on t.user_id = u.user_id
inner join users l on t.last_user_id = l.user_id
where t.creation > 1/1/2007 and forum_id=(3)
and t.votacao = case when @PageType = 'votacao' then 1
when @PageType = 'topicos' then 0
when @PageType = 'both' then end

Thank you very much Khtan for the quick reply.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-25 : 22:19:43

what about a value for when @PageType = 'todos'? or do you just want NULL in that case?

www.elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 22:25:54

what is the value of votacao in the threads table ?

try running your query with out the case when section see what are the value of threads.votacao

KH

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-25 : 22:30:58

the all code is now almost working, except by @PageType = 'todos' as you asked. @PageType = 'todos' would be in case that no matter if it’s true or false, in another words, the both together.

declare @PageType nvarchar(10)

select @PageType = 'votacao'

Select u.nick as 'user', l.nick as 'last_user', t.*
from threads t
inner join users u on t.user_id = u.user_id
inner join users l on t.last_user_id = l.user_id
where t.creation > 1/1/2007 and forum_id=(3)
and t.votacao = case when @PageType = 'votacao' then 1
when @PageType = 'topicos' then 0 end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 22:35:00

[code]
Select u.nick as 'user', l.nick as 'last_user', t.*
from threads t
inner join users u on t.user_id = u.user_id
inner join users l on t.last_user_id = l.user_id
where t.creation > @Period
and forum_id = (@forum_id)
and t.votacao = case when @PageType = 'votacao' then 1
when @PageType = 'topicos' then 0
when @PageType = 'topicos' then t.votacao
end
[/code]
Any chance that t.vatacao contains NULL value ?

KH

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-25 : 22:37:55

yahhhhh derrr, of course, thank you very much.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-26 : 09:24:00

as expected, here's my obligitory "just write it as a simple boolean expression, only rarely do you need to use CASE in your WHERE clause" post ...


where t.creation > @Period 
and   forum_id = (@forum_id) 
and   ((@PageType = 'todos') OR
       (@PageType = 'votacao' and t.votacao = 1) OR
       (@PateType = 'topicos' and t.votacao = 0))

Handles nulls fine as well, of course.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources