CODEWITHSUNDEEP
mysqlgoogle-bigquery
Nes
Nes

Reputation: 591

BigQuery Syntax Error

I'm getting this error in BigQuery: Error: syntax error at: 2:1 expecting: end of query but got: "CASE"

Query that I'm using is:

SELECT DISTINCT 

 CASE 
   WHEN
       t1.x<=t2.x 
   THEN 
       t1.x 
   ELSE
       t2.x 
 END id1, 

 CASE 
   WHEN
       t1.x<=t2.x 
   THEN 
       t2.x 
   ELSE 
       t1.x 
 END id2 

 FROM test1 t1 
 CROSS JOIN test1 t2 
 WHERE NOT t1.x = t2.x

Works in mysql but not in BigQuery.

Upvotes: 1

Views: 426

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I don't think Bigquery supports SELECT DISTINCT. Instead, use a subquery and group by:

SELECT id1, id2
FROM (SELECT (CASE WHEN t1.x <= t2.x THEN t1.x ELSE t2.x END) as id1, 
             (CASE WHEN t1.x <= t2.x THEN t2.x ELSE t1.x END) as id2 
      FROM test1 t1 CROSS JOIN
           test1 t2 
      WHERE NOT t1.x = t2.x
     ) t
GROUP BY id1, id2;

Upvotes: 6

Related Questions