在 Snowflake 函数的 where 子句中将 NULL 作为参数传递

发布于 2025-01-17 04:07:09 字数 454 浏览 3 评论 0原文

我有一个带参数的函数。如果我将 null 作为 where 子句中的参数传递，我希望它返回所有结果，例如

CREATE OR REPLACE FUNCTION ABC(" CUSTCODE" VARCHAR)
RETURNS TABLE ("CUSTOMER_CODE" VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
   SELECT  CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE  CUSTOMER_CODE = CUSTCODE
';

，如果 CUSTCODE 为 NULL 然后带回所有 CUSTOMER_CODE 几乎就像忽略 where 子句

有人能指出我正确的方向吗？

提前致谢！

莱昂

原文

I have a function with parameters. If I pass null as the parameter in a where clause I would like it to bring back all results eg

CREATE OR REPLACE FUNCTION ABC(" CUSTCODE" VARCHAR)
RETURNS TABLE ("CUSTOMER_CODE" VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
   SELECT  CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE  CUSTOMER_CODE = CUSTCODE
';

So if CUSTCODE is NULL
then bring back all CUSTOMER_CODE
almost like ignoring the where clause

Could someone point me in the right direction?

Thanks in advance!

Leon

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

朮生 2025-01-24 04:07:09

根据 Dai 的评论：

CREATE OR REPLACE TABLE DIM_CUSTOMER(CUSTOMER_CODE text, CUSTOMERNAME text);

INSERT INTO DIM_CUSTOMER VALUES ('one', 'customer one'),('two', 'customer two');

CREATE OR REPLACE FUNCTION ABC(CUSTCODE VARCHAR)
RETURNS TABLE (CUSTOMER_CODE VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
   SELECT CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE CUSTCODE IS NULL OR CUSTOMER_CODE = CUSTCODE
';

SELECT * FROM TABLE(ABC('one'));

给出：

CUSTOMER_CODE	CUSTOMERNAME
一位	客户一

SELECT * FROM TABLE(ABC(null::text));

给出：

CUSTOMER_CODE	CUSTOMERNAME
一位	客户一二
客户	二

As per Dai's comment:

CREATE OR REPLACE TABLE DIM_CUSTOMER(CUSTOMER_CODE text, CUSTOMERNAME text);

INSERT INTO DIM_CUSTOMER VALUES ('one', 'customer one'),('two', 'customer two');

CREATE OR REPLACE FUNCTION ABC(CUSTCODE VARCHAR)
RETURNS TABLE (CUSTOMER_CODE VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
   SELECT CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE CUSTCODE IS NULL OR CUSTOMER_CODE = CUSTCODE
';