CODEWITHSUNDEEP
google-bigquery
Neil D
Neil D

Reputation: 3

Google BigQuery- Unexpected keyword for WITHIN statement

I'm a new converter from Adobe Analytics to Google BigQuery and I can't seem to make the WITHIN statement work. I'm not sure if it's a system access issue or my code that's causing the error. How can I resolve this?

SELECT
  fullVisitorId,
  visitId,
  hits.hitNumber, 
  hits.time,
  max(IF(hits.customDimensions.index = 1, hits.customDimensions.value, NULL)) WITHIN RECORD as cd2n
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
  UNNEST(hits) as hits
-- GROUP BY
--   fullVisitorId, 
--   visitId,
--   hits.hitNumber,
--   hits.time
LIMIT 10
;

**ERROR CODE: Syntax error: Unexpected keyword WITHIN at [20:79]

Upvotes: 0

Views: 430

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

I can't seem to make the WITHIN statement work

WITHIN RECORD is not supported in BigQuery Standard SQL - it is rather from BigQuery Legacy SQL. See some examples of legacy SQL functions and their standard SQL equivalents in Function comparison

How can I resolve this?

Meantime, below is query that is equivalent to yours but with proper syntax (assuming that main logic in your query is correct) 

#standardSQL
SELECT
  fullVisitorId,
  visitId,
  hit.hitNumber, 
  hit.time,
  MAX(IF(customDimension.index = 1, customDimension.value, NULL)) AS cd2n 
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  LEFT JOIN UNNEST(hits) AS hit
  LEFT JOIN UNNEST(hit.customDimensions) customDimension
GROUP BY
  fullVisitorId, 
  visitId,
  hit.hitNumber,
  hit.time
LIMIT 10

Upvotes: 2

Related Questions