Using query in CASE Statement - 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

General SQL Server Forums

New to SQL Server Programming

Using query in CASE Statement

Author

Topic

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-04 : 00:40:25

Why couldn't they make SQL syntax error mistakes a little less vague.

Anyway, I was wondering, is it possible to use a set in your case statement?

CASE
(
select distinct tbhtg.TrainingBlockHistoryTypeGroupingCd
from tblTrainingBlockHistory tbh
inner join tblTrainingBlockHistoryType tbht on tbh.TrainingBlockHistoryTypeCd = tbht.TrainingBlockHistoryTypeCd
inner join tblTrainingBlockHistoryTypeGrouping tbhtg on tbht.TrainingBlockHistoryTypeGroupingCd = tbhtg.TrainingBlockHistoryTypeGroupingCd
where (select dbo.fnTrainingBlockStatus( 1234, getdate())) = tbht.TrainingBlockHistoryTypeCd
)
WHEN 'S' then (COUNT(DISTINCT TRD.TrainingBlockHistoryId) = COUNT(DISTINCT SWT.TrainingBlockHistoryId)) end

This is giving me an error on the WHEN statement. The error is "Incorrect syntax near '='" Have no idea how to fix this.
But the select statement seems to work, and as far as I can tell, that is how you write a CASE statement.

Also, this CASE statement is inside the HAVING clause - is that going to be a problem?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-04 : 00:43:45

U can not assign a value inside CASE statement, either u can try this

COUNT(DISTINCT TRD.TrainingBlockHistoryId) = CASE
(
select distinct tbhtg.TrainingBlockHistoryTypeGroupingCd
from tblTrainingBlockHistory tbh
inner join tblTrainingBlockHistoryType tbht on tbh.TrainingBlockHistoryTypeCd = tbht.TrainingBlockHistoryTypeCd
inner join tblTrainingBlockHistoryTypeGrouping tbhtg on tbht.TrainingBlockHistoryTypeGroupingCd = tbhtg.TrainingBlockHistoryTypeGroupingCd
where (select dbo.fnTrainingBlockStatus( 1234, getdate())) = tbht.TrainingBlockHistoryTypeCd
)
WHEN 'S' then COUNT(DISTINCT SWT.TrainingBlockHistoryId) end

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-04 : 00:51:38

I'm not trying to assign a value inside the CASE statement

This whole statement is wrapped inside a HAVING clause, I was basically, saying that if that select statement returns a certain value, then put the content of the THEN statement in the HAVING clause, am i going about this the wrong way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 01:13:02

quote:
Originally posted by liptonIcedTea

am i going about this the wrong way?

Perhaps, but it's hard to know with the information that you've provided. Could you describe your problem in words plus show us a data sample?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources