Snowflake过程,如果变量为true,如何在select语句中添加一列?

huangapple go评论59阅读模式
英文:

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;
    $$;

huangapple
  • 本文由 发表于 2023年7月7日 06:44:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/76632940.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定