CODEWITHSUNDEEP
sqlgoogle-bigquery
Joe Scotto
Joe Scotto

Reputation: 10857

BigQuery run different queries based on result of one?

I have no idea how to do this and have looked at the if function but can't seem to get it to work. In pseudo code

data(if (q1, truequery, falsequery)) select ... from `data`

Basically what I want to do is if a query returns a result, run one query and if not run another. Any help/lead would be great, thanks!

Upvotes: 1

Views: 1465

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

try below example for BigQuery Standard SQL 

#standardSQL
WITH tableA AS (
  SELECT 1 AS x
),
tableB AS (
  SELECT 2 AS x
),
truequery AS (
  SELECT * FROM tableA WHERE x = 1
),
falsequery AS (
  SELECT * FROM tableB
)
SELECT * FROM truequery UNION ALL 
SELECT * FROM falsequery WHERE (SELECT COUNT(1) FROM truequery) = 0

Hope this gives you good idea
Please note: output schema for both queries should be same

could I do all of this within another WITH allTables AS(...) and then run a query on that? 

#standardSQL
WITH tableA AS (
  SELECT 1 AS x
),
tableB AS (
  SELECT 2 AS x
),
truequery AS (
  SELECT * FROM tableA WHERE x = 777
),
falsequery AS (
  SELECT * FROM tableB
),
allTables AS (
  SELECT * FROM truequery UNION ALL 
  SELECT * FROM falsequery WHERE (SELECT COUNT(1) FROM truequery) = 0
)
SELECT * FROM allTables

Upvotes: 4

Related Questions