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







All Articles