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
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 605 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 696 Beast Mode
- 43 App Studio
- 39 Variables
- 660 Automate
- 170 Apps
- 442 APIs & Domo Developer
- 42 Workflows
- 6 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 388 Distribute
- 111 Domo Everywhere
- 271 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 9 Domo University
- 31 Product Releases
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive