SQL: Build table of numbers up to a variable input number inside CTE-CodePudding

I'm building a CTE in Snowflake that needs to return a table of values based on a variable derived from the max value in a table. Is it possible to set a variable in a CTE?

Here is my query that I'm working with:

WITH MAX_VAL_QUERY (
SELECT MAX(COL1) FROM SOURCE_TABLE
),

MAX_VAL_VAR_QUERY AS (
SET MAX_VAL_VAR AS (SELECT MAX_VAL FROM MAX_VAL_QUERY LIMIT 1)
),

NUMS AS (
SELECT 1 AS VAL
UNION ALL
SELECT VAL   1 AS VAL
FROM NUMS
WHERE NUMS.VAL <= MAX_VAL_VAR
)

SELECT * FROM NUMS;

Is this the right approach?

CodePudding user response：

Using GENERATOR:

SELECT ROW_NUMBER() OVER(ORDER BY SEQ4()) AS NUM
FROM TABLE(GENERATOR(ROWCOUNT=>10000)) sub  -- ROWCOUNT is arbitrary here
WHERE NUM <= (SELECT MAX(COL1) FROM SOURCE_TABLE);

Is it possible to set a variable in a CTE?

Not in a CTE but before:

SET maxval = (SELECT MAX(COL1) FROM SOURCE_TABLE);

WITH RECURSIVE NUMS AS (
  SELECT 1 AS VAL
  UNION ALL
  SELECT VAL   1 AS VAL
  FROM NUMS
  WHERE NUMS.VAL <= $maxval
)
SELECT * FROM NUMS;