What's wrong with this syntax for a computed column?
ALTER TABLE table_1
ADD [calc_column] AS
(
CASE
WHEN ([payment_1] IS NULL
AND [payment_2] IS NULL
AND [payment_3] IS NULL
AND [payment_4] IS NULL
AND [payment_5] IS NULL
AND [payment_6] IS NULL)
THEN NULL
ELSE (COALESCE([payment_1],(0)) + COALESCE([payment_2],(0)) +
CASE
WHEN (COALESCE([payment_3],(0)) + COALESCE([payment_7],(0)) + COALESCE([payment_4],(0)) > COALESCE([payment_5],(0)))
THEN (COALESCE([payment_1],(0)) + COALESCE([payment_4],(0)) + COALESCE([payment_3],(0)))
ELSE (COALESCE([payment_8],(0))) END) AS DECIMAL(13,2)
+3
source to share
1 answer
There are several bugs, mostly caused by unbalanced parentheses and missing end
for cases
. Also, you don't need to specify the type.
So, I think this will work:
ALTER TABLE table_1
ADD [calc_column] AS (CASE WHEN [payment_1] IS NULL AND
[payment_2] IS NULL AND
[payment_3] IS NULL AND
[payment_4] IS NULL AND
[payment_5] IS NULL AND
[payment_6] IS NULL
THEN NULL
ELSE COALESCE([payment_1], 0) + COALESCE([payment_2], 0) +
(CASE WHEN COALESCE([payment_3], 0) + COALESCE([payment_7], 0) + COALESCE([payment_4], 0) > COALESCE([payment_5], 0)
THEN COALESCE([payment_1], 0) + COALESCE([payment_4], 0) + COALESCE([payment_3], 0)
ELSE COALESCE([payment_8], 0)
END)
END);
+3
source to share