CODEWITHSUNDEEP
google-bigquery
Jiho Choi
Jiho Choi

Reputation: 1311

SELECT if table exists in WITH clause (BigQuery)

I am creating a table to keep continuous login days which are the results of the combination of login_[today] and continuous_login_[previous_day]. However, I want to handle the exception if the table for [previous_day] doesn't exist.

With the help of the references [1], [2], and [3], I was able to retrieve the value (continuous logins) only if the table for the previous day exists like A.

However, I wasn't able to use the result of IF EXISTS statement inside WITH clause like B. How can I use the results of the IF EXISTS in WTIH clause. Or is there any better way of doing this work?

A.

-- Retrieve only if the table exists
IF EXISTS (
        SELECT *
        FROM `my_project.my_dataset.__TABLES_SUMMARY__`
        WHERE table_id = CONCAT('_my_table_', '20201010')  -- daily log
    )
THEN
    (
        SELECT
            pid, MAX(continuous_login_days) AS continuous_login_days
        FROM `my_project.my_dataset._my_table_*`
        WHERE _TABLE_SUFFIX = '20201010'
        GROUP BY pid
    );
ELSE
    SELECT NULL;
END IF

B.

-- GOAL
WITH
...
, continuous_logins_table AS (
    [above statement]
)
SELECT
    ...
    IFNULL(continuous_logins_table.continuous_login_days, 0) + 1 AS continuous_login_days,
FROM
    ...
    LEFT JOIN continuous_logins_table

Upvotes: 0

Views: 2912

Answers (1)

MauroB
MauroB

Reputation: 580

SELECT
  pid, MAX(continuous_login_days) AS continuous_login_days
  FROM `my_project.my_dataset._my_table_*`
  WHERE _TABLE_SUFFIX = '20201010'
  AND EXISTS(
    SELECT 1
    FROM `my_project.my_dataset.__TABLES_SUMMARY__`
    WHERE table_id = CONCAT('_my_table_', '20201010')
  )
GROUP BY pid

Upvotes: 1

Related Questions