英文:
Snowflake procedure, how to add a column in a select statement if a variable is true?
问题
我对Snowflake相当新,需要一些有关语法的帮助。
如果某个变量为TRUE,我需要在SELECT语句中包含一个新列。
如果变量为FALSE,则不应添加该列。
现在我有一个过程,看起来像这样(我将尽量保持它尽可能简洁)
CREATE OR REPLACE PROCEDURE XXX (CUSTOMER_ACCOUNT_ID VARCHAR, GROUP_BY ARRAY)
RETURNS TABLE (
CUSTOMER_ACCOUNT_ID VARCHAR,
PRODUCT_NAME VARCHAR
)
LANGUAGE SQL
COMMENT = 'Test'
AS
$$
DECLARE
groupByConnector BOOLEAN;
BEGIN
-- 此变量始终接收TRUE或FALSE
groupByConnector:=ARRAY_CONTAINS('connector'::VARIANT, GROUP_BY);
res:= (
SELECT
SUM(xxx),
SUM(yyy),
IF (groupByConnector) THEN 'SUM(zzz)' END IF; -- 如何处理此问题?
FROM RANDOM_TABLE
);
RETURN TABLE(res);
END
$$;
因此,正如大家所看到的那样,'IF (groupByConnector) THEN 'SUM(zzz)' END IF;'的语法显然是错误的。我应该怎么做?已尝试阅读文档,但尚未找到解决方案。
谢谢!
英文:
I'm fairly new to snowflake and I need some help with the syntax.
I need to include a new column in a select statement if a certain variable is TRUE.
If the variable is FALSE then the column shouldnt be added.
Right now I have a procedure that looks like this (I'll try to keep it as tiny as possible)
CREATE OR REPLACE PROCEDURE XXX (CUSTOMER_ACCOUNT_ID VARCHAR, GROUP_BY ARRAY)
RETURNS TABLE (
CUSTOMER_ACCOUNT_ID VARCHAR,
PRODUCT_NAME VARCHAR
)
LANGUAGE SQL
COMMENT = 'Test'
AS
$$
DECLARE
groupByConnector BOOLEAN;
BEGIN
-- This variable always receive TRUE or FALSE
groupByConnector:=ARRAY_CONTAINS('connector'::VARIANT, GROUP_BY);
res:= (
SELECT
SUM(xxx),
SUM(yyy),
IF (groupByConnector) THEN 'SUM(zzz)' END IF; -- HOW TO DO THIS?
FROM RANDOM_TABLE
);
RETURN TABLE(res);
END
$$;
So, as you guys can see, the 'IF (groupByConnector) THEN 'SUM(zzz)' END IF;' syntax is obviously wrong. How should I do it? Tried reading the documentation but couldnt find a solution yet.
Thanks!
答案1
得分: 0
你可以使用两种方法,一种是使用动态SQL并在SQL中构建它,如果groupByConnector为True,则在SQL中包含该列,否则不包含它。另一种选项是类似于以下方式:
-- 未经测试
CREATE OR REPLACE PROCEDURE XXX (CUSTOMER_ACCOUNT_ID VARCHAR, GROUP_BY ARRAY)
RETURNS TABLE (
CUSTOMER_ACCOUNT_ID VARCHAR,
PRODUCT_NAME VARCHAR
)
LANGUAGE SQL
COMMENT = '测试'
AS
$$
DECLARE
groupByConnector BOOLEAN;
BEGIN
-- 此变量始终接收TRUE或FALSE
groupByConnector := ARRAY_CONTAINS('connector'::VARIANT, GROUP_BY);
res := (
IF groupByConnector THEN
SELECT
SUM(xxx),
SUM(yyy),
'SUM(zzz)'
FROM RANDOM_TABLE;
ELSE
SELECT
SUM(xxx),
SUM(yyy) FROM RANDOM_TABLE;
END IF;
RETURN TABLE(res);
END;
$$;
英文:
you can use 2 approaches, one is use dynamic SQL and built the SQL if groupByConnector if it is True then have the column in the SQL or else dont have it The other option is some what like this,
-- Untested
CREATE OR REPLACE PROCEDURE XXX (CUSTOMER_ACCOUNT_ID VARCHAR, GROUP_BY ARRAY)
RETURNS TABLE (
CUSTOMER_ACCOUNT_ID VARCHAR,
PRODUCT_NAME VARCHAR
)
LANGUAGE SQL
COMMENT = 'Test'
AS
$$
DECLARE
groupByConnector BOOLEAN;
BEGIN
-- This variable always receive TRUE or FALSE
groupByConnector:=ARRAY_CONTAINS('connector'::VARIANT, GROUP_BY);
res:= (
if groupByConnector THEN
SELECT
SUM(xxx),
SUM(yyy),
'SUM(zzz)'
FROM RANDOM_TABLE;
ELSE
SELECT
SUM(xxx),
SUM(yyy) FROM RANDOM_TABLE;
END if;
RETURN TABLE(res);
END;
$$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论