CODEWITHSUNDEEP
google-bigquery
peterW
peterW

Reputation: 135

Conditional logic in BigQuery scripts

I am trying to write a BigQuery statement / script which:

  1. Checks if table_a exists
  2. If table_a exists, select col_1, col_2, col_3 from table_a
  3. If table_a does not exist, select col_1, col_2, col_3 from table_b

I know how to do step 1: 

select count(1) cnt from `project.dataset.__TABLES_SUMMARY__`
WHERE table_id = 'table_a'

thanks to this post: BigQuery check if table exists or not

However, I can't do the rest of it. I have tried:

if (select count(1) cnt from `project.dataset.__TABLES_SUMMARY__`
WHERE table_id = 'table_a')=1
select col_1, col_2, col_3 from table_a

And have also tried:

if (select count(1) cnt from `project.dataset.__TABLES_SUMMARY__`
WHERE table_id = 'table_a')=1
then 
select col_1, col_2, col_3 from table_a

Both of these fail. Is this sort of conditional logic outside of a select statement possible in standard SQL?

(obviously inside of a select clause, I could have used a case statement, but I don't see how that could be used here when I want to change the table to be queried, based on a condition. And I could wrap the statement in Python code and put the condition there, but I am trying to avoid wrappers, as I wish to use the query as a saved view in BQ).

Thanks in advance.

Upvotes: 2

Views: 2359

Answers (1)

rtenha
rtenha

Reputation: 3616

The following worked for me:

DECLARE table_count int64;

set table_count = (select count(*) from dataset.INFORMATION_SCHEMA.TABLES where table_name = 'table_a');

if table_count >= 1 then (select col_1,col_2,col_3 from dataset.table_a);
else (select col_1,col_2,col_3 from dataset.table_b);
end if;

Upvotes: 1

Related Questions