CODEWITHSUNDEEP
sqlsql-serverdecimalalter-table
Ragnar
Ragnar

Reputation: 2690

ADD COLUMN with forced DECIMAL type for computed column

I'm trying to alter a to add a new computed column but the final result is decimal without ... decimal (???)

I came up with 

ALTER TABLE
  dbo.transaction_payment
ADD
  total_amount AS CAST(
    (CAST(amount_transac AS decimal(12, 2))) + (CAST(amount_tip AS decimal(12, 2))) AS decimal(12, 2)
  )

But the engine tells me that I got an error. I cast every value to don't have implicit type and lose precision and I cast the final result.

Upvotes: 0

Views: 1064

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You cannot "alter" a computed column, so:

ALTER TABLE dbo.transaction_payment
    ADD total_amount as (CONVERT(decimal(12, 2), amount_transac + amount_tip));

If it already exists, drop it first:

ALTER TABLE dbo.transaction_payment
    DROP COLUMN IF EXISTS total_amount;

Upvotes: 1

Geoff Griswald
Geoff Griswald

Reputation: 1093

Assuming you are using Microsoft SQL Server (since you have dbo. in your code), you should UPDATE the column rather than ALTER it. You appear to be missing a set of brackets around your sum:

UPDATE dbo.transaction_payment
SET total_amount = CAST((CAST(amount_transac AS DECIMAL(12,2))) + (CAST(amount_tip AS decimal(12,2)))AS decimal(12,2))

Upvotes: 0

Related Questions