Add Column Error in MySQL Transform
Hi,
I'm trying to map Google's LocationCriterionServices IDs to some Geo data from Adwords. I keep getting an error in my (case when) statement, but the SQL resources I've been checking haven't shown me what I'm doing wrong. Below is my code.
Is there another way to map 1000+ values easily in MySQL that I'm missing?
alter table `iuo_geostats_1683748494`
add COLUMN `LocationName` varchar(255) as
(
case
when geo.`CityCriteriaId` = loc.`Parent ID` then loc.`Canonical Name`
else geo.`CityCriteriaId`)
END
from `iuo_geostats_1683748494` geo
from `adwords_locationcriterionservice_08142019` loc
Comments
-
Normally the add column is a one-off process and is a separate syntax from the update clause.
From your syntax, there are two "from" and no "join" clause <- which is a clear indication of a cross join.
from `iuo_geostats_1683748494` geo
from `adwords_locationcriterionservice_08142019` locWhat I would so is a left join between the two tables and output the result to a new table instead.
select geo.*,
COALESCE( loc.`Canonical Name` , geo.`CityCriteriaId`) as LocationNamefrom `iuo_geostats_1683748494` geo
left join adwords_locationcriterionservice_08142019` loc
on geo.`CityCriteriaId` = loc.`Parent ID`
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 209 Visualize
- 257 Beast Mode
- 2.1K Charting
- 12 Variables
- 18 Automate
- 355 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 183 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive