What is the cause of this BigQuery search error?

Question

   SELECT a.name, b.name, COUNT(*) as count
   FROM (FLATTEN(
   SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name
   FROM [gdelt-bq:gdeltv2.gkg]
   WHERE DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
   ,name)) a
   JOIN EACH (
   SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name
   FROM [gdelt-bq:gdeltv2.gkg]
   WHERE DATE>20150302000000 and DATE < 20150304000000 and V2Persons like 
   '%Tsipras%'
   ) b
   ON a.GKGRECORDID=b.GKGRECORDID
   WHERE a.name<b.name
   GROUP EACH BY 1,2
   ORDER BY 3 DESC
   LIMIT 250

Here is the error message: Syntax error: Each subquery argument for table-valued function calls must be enclosed in parentheses. To fix this, replace SELECT... with (SELECT...) at [3:1]

score 2 · Accepted Answer · answered Jun 08 '20 at 17:45

The query in question is written in BigQuery Legacy SQL - so make sure you run it in Legacy mode. And secondly - below is version with few minor corrections (wrong use of double quotes instead of apostrophes in REGEXP_REPLACE)

#legacySQL
SELECT a.name, b.name, COUNT(*) AS COUNT
FROM (FLATTEN(
  SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', '')) name
  FROM [gdelt-bq:gdeltv2.gkg]
  WHERE DATE>20150302000000 
  AND DATE < 20150304000000 
  AND V2Persons LIKE '%Tsipras%' 
,name)) a
JOIN EACH (
  SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', '')) name
  FROM [gdelt-bq:gdeltv2.gkg]
  WHERE DATE>20150302000000 
  AND DATE < 20150304000000 
  AND V2Persons LIKE '%Tsipras%'
) b
ON a.GKGRECORDID=b.GKGRECORDID
WHERE a.name<b.name
GROUP EACH BY 1,2
ORDER BY 3 DESC
LIMIT 250

glad it worked for you. please also consider voting up the answer — Mikhail Berlyant, Jun 08 '20 at 19:50
I appreciate your help. This solved my problem. Thanks, Mikhail! — James Danowski, Jun 08 '20 at 19:50

What is the cause of this BigQuery search error?

1 Answers1