How do I add 2 computed columns

Question

Post reply

How do I add 2 computed columns

mw_sql_developer

SSCoach

Points: 19445
More actions
April 7, 2016 at 2:20 pm

#309774

The syntax below throws an error.
Everytime a new copy of the table [PIHP].[HIST5657] lands, I'd like to add 2 computed columns. Can you help me with the syntax
--Need to Add 2 more computed columns...
ALTER TABLE [PIHP].[HIST5657]
ADD
ServiceFromDatexVARCHAR(8) DEFAULT CONVERT(CHAR(8),ServiceFromDate,112 ),
ServiceToDatexVARCHAR(8) DEFAULT CONVERT(CHAR(8),ServiceToDate,112 )

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

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

mw_sql_developer SSCoach Points: 19445 More actions · Answer 1

Here is the error message I get :

Msg 128, Level 15, State 1, Line 3

The name "ServiceFromDate" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Correct Syntax

ALTER TABLE [PIHP].[HIST5657]

ADD

ServiceFromDatexas CONVERT(CHAR(8),ServiceFromDate,112 ),

ServiceToDatexas CONVERT(CHAR(8),ServiceToDate,112 )

Lowell SSC Guru Points: 323518 More actions · Answer 3

compulted columns are simply ADD NAME AS {some calculation } [PERSISTED] if you wantthe row values rendered and stored instead of calculated on demand

ALTER TABLE [PIHP].[HIST5657]

ADD

ServiceFromDatex AS CONVERT(CHAR(8),ServiceFromDate,112 ) PERSISTED,

ServiceToDatexAS CONVERT(CHAR(8),ServiceToDate,112 ) PERSISTED

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!