What I'm trying to do with this sql data flow is to add to my data two additional columns based on one of the metrics in the origial input. Specifically, take DMA data and get a state and region column. But I'm running into issues.
I planned to do this by editing the original data, create a new table with the desired columns, and then full outer join the tables. The first transform updates a column in the source. That's fine. But I'm having trouble with my new table. Below are a few attempts to get things to work based on browsing forms. The bolded lines are where Domo tells me I'm getting errors.
V1
Create Table blog_dma_state_region
Select RIGHT(`Metro`, 2) AS State, `Metro`
Alter Table blog_dma_state_region
ADD US_Region varchar(255)
INSERT INTO blog_dma_state_region (US_Region)
VALUE ("Unknown")
V2
Create Table blog_dma_state_region
Select RIGHT(`ga_blog_dma_agg`.`Metro`, 2) AS State, `Metro`
Alter Table blog_dma_state_region
ADD US_Region Varchar(250) DEFAULT 'Unknown' NOT NULL
V3
Create Table blog_dma_state_region
Select RIGHT(`ga_blog_dma_agg`.`Metro`, 2) AS State, US_Region, `Metro`
UPDATE blog_dma_state_region
Set US_Region = CASE WHEN IN ('TX','OK','AZ','NM') Then 'Southwest'
.
.
.
The errors are all the same - my syntax. Which is getting pretty frustrating because I don't know how UPDATE (table) is a syntax error. And because it's getting caught at the same place (despite successfully updating my input in a different transform for this flow), I'm wondering if the issue here is because I'm creating a new table and then trying to edit it in the next step.
Is this even the right way to go about things? Should my CASE WHEN be in the CREATE NEW TABLE portion? And if so, how would that look? Do I need to split this? Create the table, generate an output, and then in a different transform update it?
