sqlgoogle-bigquery

Big query multi-statement with a declare

I'm trying to UNION ALL requests (one request by quarter). I want to save this request as a view however I can't because the declare of date array return a first result and so I've multi-statement (I only want to have the second result). I don't understand why. Can you help me please ?

DECLARE i INT64 DEFAULT 1;
DECLARE dsql STRING DEFAULT '';
DECLARE rollingdate ARRAY<DATE> DEFAULT GENERATE_DATE_ARRAY("2024-01-07", DATE_ADD(CURRENT_DATE(),INTERVAL 1 QUARTER), INTERVAL 1 QUARTER);
WHILE i <= array_length(rollingdate)
DO
  SET dsql = dsql || " SELECT *, '" || rollingdate[ORDINAL(i)] || "' AS flash_date FROM table WHERE table.date = '" || rollingdate[ORDINAL(i)] || "' union all";
  SET i = i + 1;
END WHILE;
SET dsql = SUBSTR(dsql, 1, LENGTH(dsql) - LENGTH(' union all'));
EXECUTE IMMEDIATE dsql;

results

Solution

  • I found a way to avoid some of not supported commands in standard SQL :

    WITH rolling_dates AS (
  SELECT 
    DATE '2024-01-07' AS flash_date 
  UNION ALL
  SELECT DATE_ADD(DATE '2024-01-07', INTERVAL 1 QUARTER) 
  UNION ALL
  SELECT DATE_ADD(DATE '2024-01-07', INTERVAL 2 QUARTER) 
  UNION ALL
  SELECT DATE_ADD(DATE '2024-01-07', INTERVAL 3 QUARTER) 
  UNION ALL
  SELECT DATE_ADD(DATE '2024-01-07', INTERVAL 4 QUARTER) 
)
SELECT 
  * EXCEPT(rn)
FROM (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY S.id, flash_date ORDER BY provided_at DESC) AS rn
  FROM table S
  CROSS JOIN rolling_dates
  WHERE deleted.at IS NULL 
    AND provided_at <= TIMESTAMP(flash_date)
) 
WHERE rn = 1

    With this request, I can create my view and use it now.