BigQuery if table is empty return current date-CodePudding

does anyone knows how to return for example date: CURRENT_TIMESTAMP if table has no results in BigQuery?

So I can use it later to compare other tables. Currently I cannot compare "null" date to other tables date.

Example SQL query:

SELECT
    CASE 
        WHEN updated IS NULL THEN CURRENT_TIMESTAMP()
        ELSE MAX(updated) 
    END AS latest_date
FROM `my-data-test.dbsource.test_test_table`
GROUP BY updated

Result of this query is: "This query returned no results."

CodePudding user response：

Try the following:

with stub as (
    select current_date() as filler
)

select ifnull(table_data.updated, filler) as latest_date
from stub
cross join (
            select max(updated) updated from `my-data-test.dbsource.test_test_table`
            ) table_data

CodePudding user response：

group by seems unnecessary (and actually wrong) if that's the whole query and you can simlify it to this :

select coalesce(max(updated), current_timestamp()) as latest_date
from `my-data-test.dbsource.test_test_table`