Computed column

Question

Post reply

Computed column

Gareth Williams-349431

Newbie

Points: 8
More actions
March 18, 2008 at 9:04 pm

#183511

I have a computed column
[DSC] AS (case when ([item_category] = 'TANN' and ([date] >= '2004-06-24' and ([date] <= '2005-01-07'))) then ([qty] * [price]) else [dsc_sap] end) ,
which I want to change the definition.
Is there anyway I can change it or do I have to drop the column and recreate it?
Thanks
Gareth

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

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

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 1

I haven't tried it on a computed column, but I think you should be able to do an ALTER TABLE/ALTER COLUMN...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 2

... not sure it's a good idea to put hardcoded dates in a computed column, either. Not 100% sure what to replace it with but this might be a good spot to create a view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 3

My apologies... I just tried ALTER COLUMN on a computed column and it doesn't work. You'll get an "Incorrect syntax near the keyword 'AS'." You will need to drop the computed column and add it back into the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gareth Williams-349431 Newbie Points: 8 More actions · Answer 4

Thanks Jeff for your help. I thought that might be the answer but hoping not.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Gareth Williams (3/19/2008)
Thanks Jeff for your help. I thought that might be the answer but hoping not.

I have to ask - what happens to all of the previous discounts? Anything that used the previous numbers will now be "wrong" - you sure you want to do this?

Just curious.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 6

Matt Miller (3/19/2008)
Gareth Williams (3/19/2008)
Thanks Jeff for your help. I thought that might be the answer but hoping not.
I have to ask - what happens to all of the previous discounts? Anything that used the previous numbers will now be "wrong" - you sure you want to do this?
Just curious.

Ack... that's where I was going with this when I said hardcoding dates is this is a bad idea, but needed to just say it clear. Thanks, Matt... my turn to be jolted awake. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)