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.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 969 Connectors
- 256 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 500 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 370 Beast Mode
- 19 Variables
- 483 Automate
- 101 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 316 Distribute
- 64 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 63 Community Announcements
- 4.8K Archive