How do you add a computed column to a Table with correct column SQL-CodePudding

I want to add a computed column in the "DQ_MONITORING_RESULT" table. I want the status_1 to return "GREEN" if the values in COUNT_SOURCE_TABLE and "COUNT_RPT_TABLE MATCH. (Edit: I am writhing the queries in Snowflake)

This is what I have tried.

ALTER TABLE "DQ_MONITORING_RESULT"
ADD (STATUS_1) AS (CASE WHEN COUNT_SOURCE_TABLE = COUNT_RPT_TABLE THEN 'GREEN' END)

CodePudding user response：

It's poorly documented by Snowflake (in my opinion) but you can do this by setting the column default to a simple SQL statement. Look at the DEFAULT optional parameter here

CodePudding user response：

Referring to the documentation (ALTER TABLE), you must use the keyword COLUMN and provide the TYPE of the resulting column:

ADD COLUMN <col_name> <col_type> AS ( <expr> )

Example:

ALTER TABLE DQ_MONITORING_RESULT
ADD COLUMN STATUS_1 STRING AS (CASE WHEN COUNT_SOURCE_TABLE = COUNT_RPT_TABLE THEN 'GREEN' END);