Issue with 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

SQL Server 2000 Forums

Transact-SQL (2000)

Issue with CASE statement

Author

Topic

jdavidson
Starting Member

5 Posts

Posted - 2005-12-22 : 13:02:46

Good morning. I've included two pieces of code using the CASE statement that I just cannot seem to get to work. I don't know if it is because I have an AS statement at the end. The first one errors at the < and the second errors at CASE [table].[field] Any help you might have would be wonderful!!!

Issue 1:
CASE [Company Data File].[Fiscal Date]
WHEN GETDATE() < [Company Data File].[Fiscal Date]
THEN DateAdd(yyyy,-1,[Company Data File].
[Fiscal Date]
ELSE [Company Data File].[Fiscal Date]AS ADCM3,
END,

Issue 2:
CASE [Company Data File].[EE Hire Date]
WHEN GETDATE() < (CASE WHEN CONVERT(varchar(2),DATEPART(mm, [EE HireDte])) + '/'+ CONVERT(varchar(2), DATEPART(dd, [EE HireDte])) + '/' = '2/29/'
THEN '2/28/'+ CONVERT(varchar(5), DATEPART(yyyy, GETDATE()))
ELSE CONVERT(varchar(2),DATEPART(mm, [EE HireDte])) + '/'
+ CONVERT(varchar(2), DATEPART(dd, [EE HireDte])) + '/'
+ CONVERT(varchar(5), DATEPART(yyyy, GETDATE())) END)
THEN(CASE WHEN CONVERT(varchar(2),DATEPART(mm, [EE HireDte])) + '/'
+ CONVERT(varchar(2), DATEPART(dd, [EE HireDte])) + '/' = '2/29/'
THEN CONVERT(datetime, '2/28/' + CONVERT(varchar(5), DATEPART(yyyy, DATEADD(yyyy, -1, GETDATE()))))
ELSE CONVERT(varchar(2),DATEPART(mm, [EE HireDte])) + '/'
+ CONVERT(varchar(2), DATEPART(dd, [EE HireDte])) + '/'
+ CONVERT(varchar(5), DATEPART(yyyy, DATEADD(yyyy, -1, GETDATE())))END)
ELSE (CASE WHEN CONVERT(varchar(2),DATEPART(mm, [EE HireDte])) + '/'
+ CONVERT(varchar(2), DATEPART(dd, [EE HireDte])) + '/' = '2/29/'
THEN '2/28/'+ CONVERT(varchar(5), DATEPART(yyyy, GETDATE()))
ELSE CONVERT(varchar(2),DATEPART(mm, [EE HireDte])) + '/'
+ CONVERT(varchar(2), DATEPART(dd, [EE HireDte])) + '/'
+ CONVERT(varchar(5), DATEPART(yyyy, GETDATE())) END)
END AS ADCM4,

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-22 : 13:17:05

Check the syntax in BOL again. Looks like you have mixed two distinct syntaxes.

A couple of different examples

1: CASE WHEN expression THEN EXPRESSION ELSE EXPRESSION END

2: CASE MyColumn WHEN 1 THEN 'FOUND 1' WHEN 2 THEN 'FOUND 2' ELSE 'UH OH' END

jdavidson
Starting Member

5 Posts

Posted - 2005-12-22 : 14:19:56

BOL???

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-22 : 14:43:22

Books Online.

Shift + F1 from Query Analyzer

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-22 : 14:46:48

Why dont you just post some code for us and detail the problem you are trying to solve. At first glance this seems like a very complicated way to accomplish this. Perhaps if you gave us the root issue we can come up with a more elegant solution :)

Read posting hints here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Nathan Skerl

jdavidson
Starting Member

5 Posts

Posted - 2005-12-22 : 15:13:38

I just caught that Sam. :-) I also had to remove the [tablename].[fieldname] from the beginning of number 1 for it to work otherwise it was failing on the <

For number 2. Just had to remove [tablename].[fieldname] from the beginning and it also worked.!! Yea...I'm so happy!! Thanks to everyone for their insights!! Sometimes it just helps having another set of eyes!!

jdavidson
Starting Member

5 Posts

Posted - 2005-12-22 : 15:16:31

Thanks for the offer Nathan!! It is an extremely convoluted way to accomplish this...I agree. I'm in the process of upsizing an old access application and I'm trying to keep the syntax as close to the original logic until the upsizing is completed and then I can go back and modify individual queries for performance enhancements.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-22 : 16:46:46

A slightly off topic rambling about the SIMPLE CASE syntax (as opposed to SEARCHED CASE syntax)

I almost never use the simple CASE syntax, I usually use the searched CASE syntax. When I did use it a couple of times I realized there is a trap that an unsuspecting coder can fall into when one of the cases to check for is NULL. Intuitively, the code below looks like it should work but the first case condition won't successfully compare b to NULL so the ELSE value will be used. Of course it's easy to avoid this it's just that I didn't find it intuitive that "case b when null..." is equivilant to "case when b = null..."


select 	b as [BitValue]
	,case b
		when null then 'false'
		when 0    then 'false'
		          else 'true' 
	 end as [CaseResult]
from	(
	select cast(null as bit) b union all
	select cast(0    as bit) union all
	select cast(1    as bit) 
	) a

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources