个人中心
gpt4 book ai didi

Snowflake UDF Unsupported subquery type cannot be evaluated(无法评估Snowflake UDF不支持的子查询类型)

转载 作者：bug小助手 更新时间：2023-10-27 20:04:00 38 4
gpt4 key购买 nike



#Function​ snowflake database.

#函数​雪花数据库。


NOT operator in the below function in snowflake database causes UDF to return error. As per documentation NOT should be supported. What could be possible solution or work around to avoid this error.

雪花数据库中以下函数中的NOT运算符导致UDF返回错误。根据文档，不应支持。有什么可能的解决方案或解决办法来避免此错误。


CREATE OR REPLACE FUNCTION udf_page_category(p_url string)
RETURNS string
as
$$
SELECT min_by(page_category, config_rank) 
FROM config_page_category 
WHERE 1=1 
and regexp_like(p_url, )
AND (page_category_not_regex IS NULL 
OR NOT regexp_like(p_url, page_category_not_regex)
)
$$

-## Query below returns error Unsupported subquery type cannot be evaluated

-##下面的查询返回错误不支持的子查询类型无法评估


select udf_page_category( url ) FROM tablename e 
where url like '%zdfdsfsa%'

Transaction table

事务表




















ColumnA URL
First abc&welcome
Second xyz$thankyou


Config_page_category table

Config_page_category表




























page_category_regex page_category_not_regex output1
.abc. output1
.xyz. thankyou output2
.yyz. demandtor output3


NOT operator in the function causes UDF to return error in snowflake database.
first column is page_category_regex
second column is page_category_not_regex

函数中的NOT操作符导致UDF在雪花数据库中返回错误。第一列是PAGE_CATEGORY_regex，第二列是PAGE_CATEGORY_NOT_regex


function should do the following. Input argument is url, regex resides in config_page_category lookup table

函数应执行以下操作。输入参数为url，regex驻留在CONFIG_PAGE_CATEGORY查找表中



  1. url should match regex in first column and if second column is null then function should return output1

  2. url should match first column or should match second column then function should return output2

  3. url should match first column and should not match second column then function should return output3


3rd condition above is causing an issue with NOT . What could be possible solution or work around to
avoid this error.

上述第三种情况导致NOT出现问题。有什么可能的解决方案或解决办法来避免此错误。


更多回答

What error you are getting. Also give some sample data input and expected output

你得到的是什么错误。还给出了一些样本数据输入和预期输出

The classic answer is covert this to a correct join and then do the min_by and now you have your answers. While the db should be able to do this for you. Ether the optimizer cannot see how to safely do the join or it has not the pattern in its set of known translations.

经典的答案是将其转换为正确的连接，然后执行min_by，现在您就得到了答案。而数据库应该能帮你做到这一点。因此，优化器无法看到如何安全地执行联接，或者它在其已知转换集中没有该模式。

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

请澄清您的具体问题或提供额外的细节，以突出您的确切需求。就目前所写的来说，很难确切地说出你在问什么。

In your SQL you reference a column called config_rank but this is not in your table definition. Also "regexp_like(p_url, )" appears to be a typo as it is missing the second parameter

在您的SQL中，您引用了一个名为CONFIG_RANK的列，但这不在您的表定义中。另外，“regexp_like(p_url，)”似乎是一个拼写错误，因为它缺少第二个参数

优秀答案推荐
更多回答

38 4 0
文章推荐： How to make it so there isnt a horizontal scroll bar when viewport gets too small(如何使其在视区变得太小时不会出现水平滚动条)
文章推荐： awk for loop how to see if one line exists in another file and compare dates lines of both files [closed](AWK for循环如何查看另一个文件中是否存在一行并比较两个文件的日期行[已关闭])
文章推荐： Bash Completion how to ask it not to append a SPACE after current word is completed(如何让它在当前单词完成后不追加空格)
文章推荐： Django rest framework and react Cannot read properties of undefined (reading '')(Django REST框架和Reaction无法读取未定义的属性(读取‘’))
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com