Computed Column Syntax Help - 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 2012 Forums

Transact-SQL (2012)

Computed Column Syntax Help

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-08-18 : 13:47:22

I want to reference a column within the same table in a computed column specification but I can't seem to get the syntax right. For example this column will equal "Home or Board and Care" when the 'PlaceOfService' column is equal to Q5001. The PlaceOfService column is a varchar data type...not sure if I need a convert clause in my code.

Thanks in advance!

CASE WHEN [PlaceOfService]='Q5001' THEN 'Home or Board and Care' ELSE CASE WHEN [PlaceOfService]='Q5002' THEN 'Assisted Living Facility' ELSE CASE WHEN [PlaceOfService]='Q5003' THEN 'Long Term Care (Non Skilled Nursing Facility)' ELSE CASE WHEN [PlaceOfService]='Q5004' THEN 'Skilled Nursing Facility'

Mike Brown

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-18 : 13:52:32

The main problem is you need to END your case statements, like this:


select 
CASE 
		WHEN [PlaceOfService] = 'Q5001'
			THEN 'Home or Board and Care'
		ELSE CASE 
				WHEN [PlaceOfService] = 'Q5002'
					THEN 'Assisted Living Facility'
				ELSE CASE 
						WHEN [PlaceOfService] = 'Q5003'
							THEN 'Long Term Care (Non Skilled Nursing Facility)'
						ELSE CASE 
								WHEN [PlaceOfService] = 'Q5004'
									THEN 'Skilled Nursing Facility'
						END
				END
		END
END

from (values ('Q5001')) v(PlaceOfService)

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-08-18 : 14:04:18

Ahhh!! Yup, thats it. Forget the ENDS!

Thanks!

Mike Brown

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-08-18 : 15:00:18

I have one more computed column I'm having trouble with.... rather then make a new post I thought I'd write it here first...

I need to change a computed column that someone else wrote but can't seem to get it right.

Original Code:

(CONVERT([date],case when [StartDate] IS NULL then [StartTime] else [StartDate] end,(0)))

I now need the above to execute but ONLY when the column 'TOC' is equal to "CC" otherwise it should remain NULL (all columns are in same table).

I tried adding on a case when to the beginning, then an else '', and finally an end but I once again can't seem to get the syntax right...

(CASE WHEN [TOC]='CC' THEN (CONVERT([date],case when [StartDate] IS NULL then [StartTime] else [StartDate])) ELSE '' end end,(0)

Mike Brown

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-18 : 15:15:06

This time you've put a case statement inside a call to the Convert function:


CONVERT([date],case when [StartDate] ...

I'm not sure what you want but I am sure that's not it. Convert takes a datatype as the first operand and data as the second operand).

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-08-18 : 15:38:42

Thank you again ...I figured it out. This is what I wanted...

(CONVERT([char],case when [StartDate] IS NULL AND [TOC]='CC' then [StartTime]  end,(0)))

Mike Brown

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources