使用函数时不支持雪花的子查询

Question

这是我创建的函数：

CREATE OR REPLACE FUNCTION NS_REPORTS.AP."COUPA_GET_EXCH_RATE"("from_curr_id" NUMBER(38,0), "to_curr_id" NUMBER(38,0), "date" DATE)
RETURNS FLOAT
LANGUAGE SQL
AS '
    SELECT 
        COALESCE((
            SELECT 
                RATE 
            FROM 
                (
                    SELECT
                        ROW_NUMBER() OVER (PARTITION BY DATE(RATE_DATE) ORDER BY RATE_DATE DESC) ROW_NUM
                        , RATE
                    FROM
                        CONNECTORS.COUPA.EXCHANGE_RATE
                    WHERE
                        FROM_CURRENCY_ID = from_curr_id
                        AND TO_CURRENCY_ID = to_curr_id
                        AND DATE(RATE_DATE) = date
                ) R
            WHERE
                ROW_NUM = 1
        ), 1)
';

我使用ROW_NUMBER函数，因为RATE_DATE字段实际上是日期时间，因此每个日期有多个记录。

当我单独调用这个函数时，它工作得很好。但是，当我试图在视图中使用它时，我会得到不支持的子查询类型错误。没有它，风景很好。有人能想到我能做些什么来修复错误或者通过重写查询来绕过错误吗？

编辑1:查看代码和准确的错误消息

CREATE OR REPLACE VIEW COUPA_REQUISITION
AS
SELECT
    RH.ID REQ_NUM
    , RL.LINE_NUM REQ_LINE_NUM
    , OH.PO_NUMBER 
    , REPLACE(REPLACE(OH.CUSTOM_FIELDS:"legacy-po-number", '"', ''), '.0', '') LEGACY_PO_NUMBER
    , S."NAME" SUPPLIER
    , OH.STATUS 
    , UR.FULLNAME REQUESTED_BY
    , UC.FULLNAME CREATED_BY
    , OL.RECEIVED
    , DATE(RH.SUBMITTED_AT) ORDER_DATE
    , DATE(RH.NEED_BY_DATE) NEEDED_BY_DATE
    , RL."DESCRIPTION" ITEM
    , CAST(NULL AS VARCHAR) CHART_OF_ACCOUNTS
    , REPLACE(OH.CUSTOM_FIELDS:"purchase-type", '"', '') PURCHASE_TYPE
    , COM."NAME" COMMODITY
    , ACT.NS_SUB_NAME SUBSIDIARY
    , ACT.NS_ACCT_NAME_FULL "ACCOUNT"
    , ACT.NS_DEPT_NAME_FULL DEPARTMENT
    , ACT.NS_L3_DEPT_NAME L3_DEPARTMENT
    , ACT.NS_LOC_NAME "LOCATION"
    , RL.QUANTITY QTY
    , OL.LINE_NUM ORDER_LINE_NUM
    , RL.TOTAL * NS_REPORTS.AP.COUPA_GET_EXCH_RATE(RL.CURRENCY_ID, 1, DATE(RH.SUBMITTED_AT)) LINE_TOTAL
    , RL.TOTAL - OL.INVOICED UNINVOICED_AMOUNT
    , OL.INVOICED INVOICED_TOTAL
    , RLSUM.TOTAL TOTAL
    , REPLACE(IL.CUSTOM_FIELDS:"amortization-schedule"."name", '"', '') AMORTIZATION_SCHEDULE
    , CASE WHEN COALESCE(IL.CUSTOM_FIELDS:"amortization-start-date", '') <> '' THEN DATE(REPLACE(IL.CUSTOM_FIELDS:"amortization-start-date", '"', '')) ELSE NULL END AMORTIZATION_START_DATE
    , CASE WHEN COALESCE(IL.CUSTOM_FIELDS:"amortization-end-date", '') <> '' THEN DATE(REPLACE(IL.CUSTOM_FIELDS:"amortization-end-date", '"', '')) ELSE NULL END AMORTIZATION_END_DATE
    , CASE WHEN COALESCE(OH.CUSTOM_FIELDS:"contract-start-date", '') <> '' THEN DATE(REPLACE(OH.CUSTOM_FIELDS:"contract-start-date", '"', '')) ELSE NULL END CONTRACT_START_DATE
    , CASE WHEN COALESCE(OH.CUSTOM_FIELDS:"contract-end-date", '') <> '' THEN DATE(REPLACE(OH.CUSTOM_FIELDS:"contract-end-date", '"', '')) ELSE NULL END CONTRACT_END_DATE
FROM 
    CONNECTORS.COUPA.REQUISITION_HEADER RH
    JOIN CONNECTORS.COUPA.REQUISITION_LINE RL ON RL.REQUISITION_HEADER_ID = RH.ID
    JOIN NS_REPORTS.AP.COUPA_ACCOUNT ACT ON ACT.COUPA_ACCT_ID = RL.ACCOUNT_ID
    JOIN CONNECTORS.COUPA."USER" UR ON UR.ID = RH.REQUESTED_BY_ID 
    JOIN CONNECTORS.COUPA."USER" UC ON UC.ID = RH.CREATED_BY_ID 
    JOIN (
        SELECT
            REQUISITION_HEADER_ID 
            , SUM(TOTAL) TOTAL
        FROM
            CONNECTORS.COUPA.REQUISITION_LINE 
        GROUP BY
            REQUISITION_HEADER_ID 
    ) RLSUM ON RLSUM.REQUISITION_HEADER_ID = RH.ID
    LEFT JOIN CONNECTORS.COUPA.ORDER_LINE OL ON OL.ID = RL.ORDER_LINE_ID 
    LEFT JOIN CONNECTORS.COUPA.ORDER_HEADER OH ON OH.ID = OL.ORDER_HEADER_ID 
    LEFT JOIN CONNECTORS.COUPA.COMMODITY COM ON COM.ID = OL.COMMODITY_ID  
    LEFT JOIN CONNECTORS.COUPA.SUPPLIER S ON S.ID = OH.SUPPLIER_ID 
    LEFT JOIN CONNECTORS.COUPA.INVOICE_LINE IL ON IL.ORDER_LINE_ID = OL.ID 

错误消息: SQL 2031: SQL编译错误:无法计算不支持的子查询类型

Answer 1

错误是一个相关的子查询，不支持(除了一些小玩具示例)。

但基本形式是

SELECT a.a
       (select b.b from b where b.a = a.a order by b.y limit 1)
FROM a;

实际上，对于每一行，都会在另一个表上运行子查询来获取值。在其他DB中，有许多技巧可以使这个“工作”，但实际上，每一行都做了一些工作。雪花并不是每一行操作的类型。

好消息是，还有其他模式实际上是相同的，雪花确实支持，这两个模式实际上是相同的使用一个CTE或连接到一个子选择，这是相同的事情。

因此，上述各点如下：

WITH subquery AS (
    SELECT b.a, b.b FROM b
    QUALIFY row_number() over (partition by b.a order by b.y) = 1
)
SELECT a.a
    sq.b
FROM a
JOIN subquery AS sq 
    ON sq.a = a.a

因此，我们首先处理/形状来自另一个/子表的“所有记录”，并且只保留具有我们想要的计数/形状的行，然后连接到该结果。它是非常可并行的，所以性能很好。雪花不为你自动翻译一个子查询的原因是，它很容易出错吗?他们目前正在那里进行开发，致力于开发根本不存在的特性等等，如果你理解你的模型，它可以由你重写。

Answer 2

如果把这个移到FROM子句中呢？我可以这样说：

SELECT COALESCE(MAX(er.rate), 1)
FROM (SELECT er.*
      FROM CONNECTORS.COUPA.EXCHANGE_RATE er
      WHERE er.FROM_CURRENCY_ID = in_from_curr_id AND
            er.TO_CURRENCY_ID = in_to_curr_id AND
            DATE(er.RATE_DATE) = in_date
      ORDER BY RATE_DATE DESC
      LIMIT 1
     ) er;

注意，我更改了参数的名称，因此它们是更明显的输入参数。