Snowflake DDL created is wrong with ' execute immediate create view '

Question

Query:

execute immediate '<--create view query-->';

Resultant DDL: create or replace view view_name( <--columnlist--> ) asexecute immediate '<--create view query-->';

Example: Query:

execute immediate 'create or replace view a as select * from b';

DDL: create or replace view a(b1,b2,b3) asexecute immediate 'create or replace view a as select * from b';

Answer 1

Well spotted. To reproduce this case using Snowsight:

EXECUTE IMMEDIATE 'create or replace view a as select 1 AS c';
-- View A successfully created.

SELECT * FROM a;
-- C
-- 1

SELECT GET_DDL('VIEW', 'PUBLIC.A');

create or replace view A( C ) asEXECUTE IMMEDIATE 'create or replace view a as select 1 AS c';

---

Workaround. Using anonymous block to wrap view definition:

EXECUTE IMMEDIATE $$
BEGIN
  create or replace view a as select 1 AS c;
END;
$$;
-- null

SELECT * FROM a;
-- C
-- 1

SELECT GET_DDL('VIEW', 'PUBLIC.A');

create or replace view A( C ) as select 1 AS c;