CASE structure trouble - 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 2000 Forums

Transact-SQL (2000)

CASE structure trouble

Author

Topic

alsu50
Starting Member

3 Posts

Posted - 2004-07-06 : 20:00:35

Hi! I need to figure out if a student is proficient.

I have the following query:


declare @test char(1) --type of test
declare @subj varchar(5) --subject, possible values: rdng, write, math, all
declare @sql varchar(3000) --query
declare @flag char(1) --indicates either search is for Proficient or non Proficient, values: Y N
set @test = 'C'
set @subj = 'all'
set @flag = 'Y'
set @sql = 'Select * from RollupScores where
CASE 
WHEN ''' + @subj + ''' = ''rdng'' THEN ReadingProf  
WHEN ''' + @subj + ''' = ''write'' THEN WritingProf 
WHEN ''' + @subj + ''' = ''math'' THEN MathProf
ELSE  1 = 1
END
if (''' + @subj + ''' <> ''all'') 
BEGIN
	CASE 
	WHEN ' + @flag + ' = ''Y'' THEN  IN (''AP'', ''BP'')
	ELSE NOT IN (''AP'', ''BP'')
	END
END'

print(@sql)
exec(@sql)

which gives me the following constructed query with errors:


Select * from State_RollupScores where
CASE 
WHEN 'all' = 'rdng' THEN ReadingProf  
WHEN 'all' = 'write' THEN WritingProf 
WHEN 'all' = 'math' THEN MathProf
ELSE  1 = 1
END
if ('all' <> 'all') 
BEGIN
	CASE 
	WHEN Y = 'Y' THEN  IN ('AP', 'BP')
	ELSE NOT IN ('AP', 'BP')
	END
END
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CASE'.

What am I doing wrong and how can I reach my goal?

Thank you.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-06 : 20:07:03

Can you post some sample data and an expected resultset?

alsu50
Starting Member

3 Posts

Posted - 2004-07-06 : 20:17:43

Sample table elements: Name, Test (possible values: A, B, C), ReadingProf (AP, BP, NP), MathProf (AP, BP, NP), WritingProf (AP, BP, NP).

I need to figure out all Names of people who either have AP or BP in either one of 3 criteria (ReadingProf or WritingProf or MathProf) or all of them at once, or who don't have AP or BP in either one of 3 criteria (ReadingProf or WritingProf or MathProf) or all of them at once.

I hope it makes sense

alsu50
Starting Member

3 Posts

Posted - 2004-07-06 : 20:59:03

Nevermind, I figured it out.

The whole logic of the query was wrong. But I think the problem was with using CASE statement, does anyone know of any occasions on which it would not work?

For solving my problem, I used IF statements. Works fine.

Thanks anyway.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-07 : 09:11:47

quote:
Select * from State_RollupScores where
CASE
WHEN 'all' = 'rdng' THEN ReadingProf
WHEN 'all' = 'write' THEN WritingProf
WHEN 'all' = 'math' THEN MathProf
ELSE 1 = 1
END
if ('all' <> 'all')
BEGIN
CASE
WHEN Y = 'Y' THEN IN ('AP', 'BP')
ELSE NOT IN ('AP', 'BP')
END
END
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CASE'.

Syntax error in bold. Move the "= 1" after the END. The second part will never run as is. I'm not sure what you're trying to do there because the word 'all' will most likely never not equal 'all'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources