Why I get a "Syntax error:L compilation error: (line …" when trying to create a SNOWFLAKE UDTF which use IDENTIFIER function?

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

Why I get a "Syntax error:L compilation error: (line ..." when trying to create a SNOWFLAKE UDTF which use IDENTIFIER function?

问题

我尝试创建一个Snowflake UDTF时,为什么会出现“Syntax error: L compilation error: (line ...)”错误?如果我注释掉这行:“AND IDENTIFIER(baseline) = I.COMP”,函数就能正确编译,但不会生成预期的结果。

SQL是该函数的处理程序。

CREATE OR REPLACE FUNCTION fn_Shopper_Insights(dx_id varchar, business varchar, baseline varchar)    
   RETURNS TABLE(name varchar, value varchar)    
   LANGUAGE SQL    
   AS    
$$    
   SELECT V.name, V.value    
   FROM (    
         SELECT name, value    
         FROM (    
           SELECT    
               Q1.GRP AS NAME,    
               Q1.AGG AS VALUE,    
               ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK    
           FROM (             
            
                 -- demographics    
                 (    
                   SELECT    
                       D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX    
                   FROM store_pro_demos_long D    
                     INNER JOIN index_dim A USING(DX_ID)    
                     INNER JOIN index_demos_pct I    
                       ON D.MVS_DESC = I.MVS_DESC    
                       AND IDENTIFIER(baseline) = I.COMP    
                       AND I.BASELINE = baseline    
                   WHERE D.DX_ID = dx_id
                 )
           ) Q1
         ) WHERE RNK = 1
       ) V
$$;
英文:

Why I get a "Syntax error:L compilation error: (line ..." when trying to create a Snowflake UDTF? If I comment the line: "AND IDENTIFIER(baseline) = I.COMP" the function compiles correctly but do not generate the expected results.

SQL is the handler for the function.

CREATE OR REPLACE FUNCTION fn_Shopper_Insights(dx_id varchar, business varchar, baseline varchar)    
   RETURNS TABLE(name varchar, value varchar)    
   LANGUAGE SQL    
   AS    
$$    
   SELECT V.name, V.value    
   FROM (    
         SELECT name, value    
         FROM (    
           SELECT    
               Q1.GRP AS NAME,    
               Q1.AGG AS VALUE,    
               ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK    
           FROM (             
                 -- demographics    
                 (    
                   SELECT    
                       D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX    
                   FROM store_pro_demos_long D    
                     INNER JOIN index_dim A USING(DX_ID)    
                     INNER JOIN index_demos_pct I    
                       ON D.MVS_DESC = I.MVS_DESC    
                       AND IDENTIFIER(baseline) = I.COMP    
                       AND I.BASELINE = baseline    
                   WHERE D.DX_ID = dx_id
                 )
           ) Q1
         ) WHERE RNK = 1
       ) V
$$;

答案1

得分: 1

三级嵌套是不必要的,可以简化为:

SELECT    
    D.PROJECT_TYPE AS GRP, 
    D.MVS_DESC AS AGG
FROM store_pro_demos_long AS D    
JOIN index_dim AS A USING(DX_ID)    
JOIN index_demos_pct AS I    
  ON D.MVS_DESC = I.MVS_DESC    
 AND IDENTIFIER(baseline) = I.COMP    
 AND I.BASELINE = baseline    
WHERE D.DX_ID = dx_id
QUALIFY ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY DIV0NULL(D.PCT, I.PCT) DESC)=1;

第二个 IDENTIFIER(baseline) 可以替换为 CASE 表达式:

AND IDENTIFIER(baseline) = I.COMP
<=>
AND I.COMP = CASE WHEN baseline = 'col_name1' THEN D.col_name1
                  WHEN baseline = 'col_name2' THEN D.col_name2
                  ELSE D.col_name_default
             END

注意:代码部分未被翻译。

英文:

Three level nesting is unnecessary and could be simplified:

SELECT V.name, V.value    
FROM (    
     SELECT name, value    
     FROM (    
       SELECT    
           Q1.GRP AS NAME,    
           Q1.AGG AS VALUE,    
           ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK    
       FROM (             
             -- demographics    
             (    
               SELECT    
                   D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX    
               FROM store_pro_demos_long D    
                 INNER JOIN index_dim A USING(DX_ID)    
                 INNER JOIN index_demos_pct I    
                   ON D.MVS_DESC = I.MVS_DESC    
                   AND IDENTIFIER(baseline) = I.COMP    
                   AND I.BASELINE = baseline    
               WHERE D.DX_ID = dx_id
             )
       ) Q1
     ) WHERE RNK = 1
   ) V

To:

SELECT    
    D.PROJECT_TYPE AS GRP, 
    D.MVS_DESC AS AGG
FROM store_pro_demos_long AS D    
JOIN index_dim AS A USING(DX_ID)    
JOIN index_demos_pct AS I    
  ON D.MVS_DESC = I.MVS_DESC    
 AND IDENTIFIER(baseline) = I.COMP    
 AND I.BASELINE = baseline    
WHERE D.DX_ID = dx_id
QUALIFY ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY DIV0NULL(D.PCT, I.PCT) DESC)=1;

Second IDENTIFIER(baseline) could be replaced with CASE expression:

AND IDENTIFIER(baseline) = I.COMP
&lt;=&gt;
AND I.COMP = CASE WHEN baseline = &#39;col_name1&#39; THEN D.col_name1
                  WHEN baseline = &#39;col_name2&#39; THEN D.col_name2
                  ELSE D.col_name_default
             END

答案2

得分: 0

我在考虑使用您的建议,将 IDENTIFIER 函数替换为 dbt 和 Jinja。我将创建UDTF作为dbt模型,然后使用pre_hookmacro来查询不同的基线,并使用jinja动态生成CASE语句:

AND IDENTIFIER(baseline) = I.COMP
<=> 
AND I.COMP = CASE {%- for b in baselines -%}
                   WHEN baseline = {{b}} THEN D.{{b}}
                  {%- endfor -%}
                   ELSE D.col_name_default
             END
英文:

I am thinking in using your recommendation of replacing IDENTIFIER function with dbt and Jinja. I will create the UDTF as a dbt model, and then use a pre_hook or a macro to query the different baselines and generate the case dynamically with jinja:

AND IDENTIFIER(baseline) = I.COMP
&lt;=&gt;
AND I.COMP = CASE {%- for b in baselines -%}
                   WHEN baseline = {{b}} THEN D.{{b}}
                  {%- endfor -%}
                   ELSE D.col_name_default
             END

huangapple
  • 本文由 发表于 2023年6月22日 03:55:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76526722.html
匿名

发表评论

匿名网友

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

确定