Declare variables in scheduled query BigQuery;-CodePudding

I am developing a scheduled query where I am using the WITH statement to join and filtrate several tables from BigQuery. To filtrate the dates, I would like to declare the following variables: DECLARE initial, final DATE;

SET initial = DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH) 7,ISOWEEK);
SET final = LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH) 7, ISOWEEK);

However, when executing this query, I am getting two results; one for the variables declared (which I am not interested in having them as output), and the WITH statement that is selected at the end (which as the results that I am interested in).

The principal problem is that, whenever I try t connect this scheduled query to a table in Google Data Studio I get the following error:

Invalid value: configuration.query.destinationTable cannot be set for scripts;

How can I declare a variable without getting it as a result at the end?

Here you have a sample of the code I am trying work in:

DECLARE initial, final DATE;
SET initial = DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH) 7,ISOWEEK);
SET final = LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH) 7, ISOWEEK);
WITH HelloWorld AS (

SELECT shop_date, revenue
FROM fulltable
WHERE shop_date >= initial
  AND shop_date <= final

)
SELECT * from HelloWorld;

CodePudding user response：


with initial1 as ( select DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH) 7,ISOWEEK) as initial2),

final1 as ( select LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH) 7, ISOWEEK) as final2),

HelloWorld AS (
SELECT shop_date, revenue
FROM fulltable
WHERE shop_date >= (select initial2 from initial1) AND shop_date <= (select final2 from final1)
)

SELECT * from HelloWorld;

CodePudding user response：

With config table having just 1 row and cross-joining it with your table, your query can be written like below.

WITH config AS (
  SELECT DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH) 7,ISOWEEK) AS initial,
         LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH) 7, ISOWEEK) AS final
),
HelloWorld AS (
  SELECT * FROM UNNEST([DATE '2022-06-06']) shop_date, config
   WHERE shop_date >= config.initial AND shop_date <= config.final
)
SELECT * FROM HelloWorld;