What is LEVEL equivalent in snowflake

Question

I have to convert one oracle query to snowflake,which has a where clause LEVEL > 1. Could you please suggest me the best option.

Thanks.

Answer 1

I don't think it's an exact match, but the closest thing is the "start with" clause of Snowflake's connect by:

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...

You can provide a where clause on the start with predicate, but without the "where" keyword. You can read more about it here: https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html

Answer 2

There is level in snowflake. The differences from Oracle are:

• In snowflake it's neccesary to use prior with connect by expression.
• And you can't just select level - there should be any existing column in the select statement.

Example:

SELECT LEVEL, dummy FROM 
    (select 'X' dummy ) DUAL 
CONNECT BY prior LEVEL <= 3;

LEVEL   DUMMY
1   X
2   X
3   X
4   X