请求协助将Oracle查询语句转换为Snowflake查询语句

问题背景 #

需要将以下Oracle查询转换为Snowflake兼容的语句：

select level,dummy,length(dummy)
from (select 'XYZ' dummy from dual
      union 
      select 'YS' dummy from dual
    ) dual
connect by level < length(dummy)

Oracle原查询输出 #

该查询会生成以下结果行：

• LEVEL=1, DUMMY='XYZ', LENGTH(DUMMY)=3
• LEVEL=2, DUMMY='XYZ', LENGTH(DUMMY)=3
• LEVEL=1, DUMMY='YS', LENGTH(DUMMY)=2

Snowflake等价实现 #

Snowflake不支持Oracle的CONNECT BY LEVEL语法，可通过UNNEST结合RANGE函数实现相同逻辑，这是最简洁的方式：

SELECT
    n AS level,
    t.dummy,
    LENGTH(t.dummy) AS length_dummy
FROM (
    SELECT 'XYZ' AS dummy
    UNION ALL
    SELECT 'YS' AS dummy
) t,
UNNEST(RANGE(1, LENGTH(t.dummy))) AS n;

关键说明

1. 序列生成：RANGE(1, LENGTH(t.dummy))会生成从1开始、到LENGTH(t.dummy)-1结束的整数序列，完全匹配Oracle中level < length(dummy)的行生成逻辑。
2. 性能优化：用UNION ALL替代原查询的UNION，由于两个子查询无重复值，UNION ALL避免了不必要的去重操作，性能更优且结果一致。

如果需要兼容更大的长度范围，也可以用GENERATOR函数结合LATERAL的写法：

SELECT
    seq + 1 AS level,
    t.dummy,
    LENGTH(t.dummy) AS length_dummy
FROM (
    SELECT 'XYZ' AS dummy
    UNION ALL
    SELECT 'YS' AS dummy
) t,
LATERAL (
    SELECT seq 
    FROM TABLE(GENERATOR(ROWCOUNT => 100))
    WHERE seq < LENGTH(t.dummy) - 1
) gen;

内容的提问来源于stack exchange，提问作者siddhartha jain