在Snowflake中,我试图根据条件限制行数。查询是从应用程序生成的。
我一直在努力实现的逻辑。
让我们把a作为行号计数,如果它是-1,那么我想检索所有的行。
SELECT * FROM "PUBLIC".TABLE1 LIMIT (
CASE WHEN a = -1
THEN
-- no limit
ELSE
aa将被解析为按应用程序编号。这种手术在雪花里是可能的吗?
上面的查询在雪花中不起作用,只是添加了它来指定需求
发布于 2022-02-22 05:52:27
WITH sample_data AS (
SELECT SEQ4()+1 AS val, 100 AS a
FROM TABLE(GENERATOR(ROWCOUNT => 1000)) AS t
)
SELECT ROW_NUMBER() OVER(ORDER BY val) AS rn
FROM sample_data
QUALIFY rn <= IFF(a = -1, rn, a);结果:100 rows
WITH sample_data AS (
SELECT SEQ4()+1 AS val, -1 AS a
FROM TABLE(GENERATOR(ROWCOUNT => 1000)) AS t
)
SELECT ROW_NUMBER() OVER(ORDER BY val) AS rn
FROM sample_data
QUALIFY rn <= IFF(a = -1, rn, a);结果:1000 rows --无限制,所有行
发布于 2022-02-22 06:02:46
您可以使用SQL过程 (在公共预览中):
create or replace table test (a int);
insert into test values (1), (2), (3), (4), (5);
select * from test;
create or replace procedure dynamic_limit(limit int)
returns table(a int)
language sql
as
$$
declare
query string default 'SELECT * FROM TEST';
begin
IF (limit > 0) THEN
query := :query || ' LIMIT ' || :limit;
END IF;
let res resultset default (execute immediate :query);
return table(res);
end;
$$;
call dynamic_limit(-1);
+---+
| A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
call dynamic_limit(0);
+---+
| A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
call dynamic_limit(1);
+---+
| A |
|---|
| 1 |
+---+
call dynamic_limit(4);
+---+
| A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
+---+https://stackoverflow.com/questions/71216073
复制相似问题