Using values inside a cte in snowflake queries-CodePudding

I'm trying to use values inside a cte in snowflake. For example, this:

WITH person (id, name)
     AS (VALUES (1, 'Ted'), (2, 'Bill'))
SELECT *
FROM   person;

works fine in (for example) postgres, but gives me the following error in snowflake:

SQL compilation error: syntax error line 2 at position 9 unexpected 'VALUES'. syntax error line 2 at position 17 unexpected '1'. syntax error line 2 at position 26 unexpected ','.

However, from snowflake documentation I can see VALUES syntax is supported, for example this works in snowflake:

SELECT * FROM (VALUES (1, 'Ted'), (2, 'Bill')) as person (id, name);

So I'm wondering how I'd get it to work inside a cte.

I would like the same output as I would get from postgres (using a cte)

 id | name 
---- ------
  1 | Ted
  2 | Bill
(2 rows)

CodePudding user response：

You have to add a SELECT statement inside your CTE

WITH person AS (
    SELECT $1 AS id,
           $2 AS name
    FROM (VALUES (1, 'Ted'), (2, 'Bill'))
)
SELECT *
FROM   person;

CodePudding user response：

You can define table and column alias like this:

select * from (values (1, 'One'), (3, 'three')) as person (ID, NAME);

And then of course reference the table expression in a CTE:

WITH PERSON as
(
    select * from (values (1, 'One'), (3, 'three')) as person (ID, NAME)
)
SELECT * FROM person;

CodePudding user response：

You were close

with person (id, name) as 
(select * from values (1, 'ted'), (2, 'bill'))

select *
from person;