SQL server Computed Column on Alter statement

Question

Post reply

SQL server Computed Column on Alter statement

kdaniapam

Say Hey Kid

Points: 705
More actions
March 10, 2016 at 11:30 am

#313925

I am trying to create a sql server computed column with an alter statement below,but I can't seem to get it working. Any help will be appreciated.
ALTER TABLE Schedule ADD
[SubmitDate] AS
IF(AND(REVIEWDueStatus=10 AND IsNull(ReviewDate_Actual ))
"<DIV>
<table><tr><td style='vertical-align:top;text-
align:left;'>Due:</td><td valign='top' align='left'> "&TEXT([ReviewDate],"dd mmm yyyy")&"</td>
</tr>
<tr><td style='vertical-align:top;text-
align:left;'>Act:</td><td valign='top'
align='left'>NR</td></tr></table></DIV>",
IF(ReviewDueStatus=10,"<DIV>
<table><tr><td style='vertical-align:top;text-align:left;'>Due:</td>
<td valign='top' align='left'> "&TEXT([ReviewDate],"dd mmm yyyy")&"</td>
</tr><tr><td style='vertical-align:top;text-
align:left;'>Act:</td><td valign='top' align='left'> "&TEXT([ReviewDate_Actual],"dd mmm yyyy")&"</td>
<td>NR</td></tr></table></DIV>", ""))

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

a calculated column cannot have an if statement; you canuse a CASE though

something like this is what i think you wnat:

SET QUOTED_IDENTIFIER ON

ALTER TABLE Schedule ADD

[SubmitDate] AS

CASE

WHEN REVIEWDueStatus=10 AND ReviewDate_Actual IS NOT NULL

THEN

'<DIV>

<table><tr><td style=''vertical-align:top;text-

align:left;''>Due:</td><td valign=''top'' align=''LEFT''> "&TEXT([ReviewDate],"dd mmm yyyy")&"</td>

</tr>

<tr><td style=''vertical-align:top;text-

align:left;''>Act:</td><td valign=''top''

align=''left''>NR</td></tr></table></DIV>'

WHEN ReviewDueStatus=10

THEN '<DIV>

<table><tr><td style=''vertical-align:top;text-align:left;''>Due:</td>

<td valign=''top'' align=''left''> "&TEXT([ReviewDate],"dd mmm yyyy")&"</td>

</tr><tr><td style=''vertical-align:top;text-

align:left;''>Act:</td><td valign=''top'' align=''left''> "&TEXT([ReviewDate_Actual],"dd mmm yyyy")&"</td>

<td>NR</td></tr></table></DIV>'

ELSE ''

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 77014 More actions · Answer 2

I think that you would be better off creating a view to do this than creating a computed column. In a view, you will be able to use FOR XML to help construct your HTML, which will prevent the errors that I'm receiving when trying to parse your data as XML.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA