0

I am trying to add a column based on a case when statement mapping status to status names. This is in Databricks, and i'm wondering if i'm having an issue with permissions or if my syntax is off somewhere. A similar statement worked in SQL server.

    ALTER TABLE dbo.table
    ADD status_name as (CASE        WHEN status = '0' THEN 'Pink'
                                    WHEN status = '9' THEN 'Green'
                                    WHEN status = '8' THEN 'Blue'
                                    WHEN status = '2' THEN 'Red'
                                    ELSE 'Other'
                                    END);

This is the error message I am getting:

Error in SQL statement: ParseException: no viable alternative at input 'ALTER TABLE db.table\nADD status_name'(line 2, pos 4)

Anyone know where i'm going wrong here? Shouldn't the syntax be identical between data-bricks and sql server? The table names are different in Databricks.

CharlieBitMaFinga
  • 35
  • 6
  • Definitely not a permissions issue it's a syntax problem. Where is your table located? on Databricks or SQL Server? `ADD COLUMN This clause is not supported for JDBC data sources. Adds one or more columns to the table, or fields to existing columns in a Delta Lake table.` – The Singularity Nov 05 '22 at 00:42
  • The table is in Databricks. If I query the db by referencing the db.table, it works. But suddenly when I try to add a column it won’t work. So i’m judt trying to see if there’s a syntax difference between databricks and sql server or if it’s something else… looking like i’ll just have to ask a coworker haha – CharlieBitMaFinga Nov 05 '22 at 02:02

1 Answers1

1

I noticed that you missed COLUMN in your statement.

ALTER TABLE dbo.table
ADD **COLUMN** status_name
Pat S
  • 11
  • 2