none
Computed Column - Error Validating Formula RRS feed

  • Question

  •  

    I want to create a computed column with this formula:

     

    ISNULL(NULLIF (tot_mnc, 0) / NULLIF (repl_value, 0), 0)

     

    It works in a straight select query, but when I put it in the formula of the table design window, I get an error "Error validating the formula for column 'test_fci'"

     

    I don't know if it's relevant but repl_value is itself a computed column with the formula:

     

     (repl_value_e_g + repl_value_aux)

     

    Is it possible to use the system functions in a computed column?  If not, how would I pass those values into a udf and use it for the formula?

     

    Thanks.

    Thursday, September 13, 2007 4:45 PM

Answers

  • You are correct: you are not allowed to create a computed column that is based on a different computed column.  You can use the built-in functions, so just create your new computed column based on the calculations from the base columns.

     

    If you were to run an ALTER statement to create your computed column based on a computed column you would potentially get an error message like:

     

    Msg 1759, Level 16, State 0, Line xxxx

    Computed column 'ccccc' in table 'tttttt' is not allowed to be used in another computed-column definition.

     

     

    Kent

    Thursday, September 13, 2007 5:01 PM
    Moderator

All replies

  • You are correct: you are not allowed to create a computed column that is based on a different computed column.  You can use the built-in functions, so just create your new computed column based on the calculations from the base columns.

     

    If you were to run an ALTER statement to create your computed column based on a computed column you would potentially get an error message like:

     

    Msg 1759, Level 16, State 0, Line xxxx

    Computed column 'ccccc' in table 'tttttt' is not allowed to be used in another computed-column definition.

     

     

    Kent

    Thursday, September 13, 2007 5:01 PM
    Moderator
  • That was it, thank you.  The table designer gui hid the details of the error message and I didn't know which of several possiblities were causing the error.

     

    Thanks again.

     

    Thursday, September 13, 2007 6:12 PM