Creating a Snowflake View with a Sequence

user_3079886 0
Viewed 200

Is it possible to use a Sequence inside a View?? I am using the following query:

CREATE VIEW < VIEW_NAME > (ID, VALUE1, VALUE2,...) AS 
    SELECT 
      SEQ1.NEXTVAL, 
      VAL1, 
      VAL2, 
      ...
    FROM 
   < TABLE >

But it is giving me the following error:

invalid identifier 'SEQ1.NEXTVAL'

The query works when not creating the view: enter image description here

snowflake-cloud-data-platform sequence view
3 Answers

use the full qualified name for the SEQ object, because if I am in a different DB/Schema, that is the scope used to look for SEQ

basically everything in a view should be fully qualified, tables, views, functions, sequences.

CREATE DATABASE test;
create SCHEMA test.test;
use SCHEMA test.test;
create SEQUENCE seq1;

create view test_v as SELECT seq1.nextval;

select * from test.test.test_v;

gives:

NEXTVAL
2 
create SCHEMA test.not_test;
use SCHEMA test.not_test;

select * from test.test.test_v;

and now you get:

SQL compilation error: error line 1 at position 29 invalid identifier 'SEQ1.NEXTVAL'

user_439920
user_44445770

Is the Sequence existing? Looks like you accidentally missed to create it.

user_124804310
Related