BigQuery Syntax Error with "("

Question

Problem

I'm creating a query to uniformly distribute Facebook ad spend amount per ad id's.

Syntax stopped me.

SELECT 
t.lead_city, 
t.lead_time,
t.facebook_ad_id,
t.facebook_leadads_id,
t.id_promo,
t.value,
(d.spent / CAST((SELECT COUNT(*) as cnt FROM `unified.FF_FACEBOOKLEADADS_DISCOUNTS_VISITS` t2 where t.facebook_ad_id = t2.facebook_ad_id) AS decimal(10,2))) as DistributedSpend
FROM `unified.FF_FACEBOOKLEADADS_DISCOUNTS_VISITS` t
INNER JOIN `unified.ads_data` d
ON d.ad_id = t.facebook_ad_id

This gives an error of "Expected ")" but got "(" at [24:178]". Can anyone direct to a BigQuery syntax resource or point out the mistake I'm making?

You should provide sample data, desired results, and an explanation of what you are trying to do. — Gordon Linoff
– Gordon Linoff, Commented Jun 18, 2018 at 17:02

Alan Pearson · Accepted Answer · 2018-06-18 17:05:37Z

3

DECIMAL(10,2) is not a valid BigQuery datatype. Try using NUMERIC data type instead.

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

answered Jun 18, 2018 at 17:05

Alan Pearson

661 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BigQuery Syntax Error with "("

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related