错误消息为 [Amazon](500310) 无效操作:由于内部错误，不支持这种类型的相关子查询模式

Question

这是查询

CREATE TABLE dw_prestage.check_fact_update
AS
SELECT transaction_id,
transaction_line_id,
subsidiary_id
FROM    ( SELECT transaction_id,
transaction_line_id,
subsidiary_id
FROM( SELECT 
document_number         ,
transaction_number      ,
transaction_id          ,
transaction_line_id     ,
transaction_order       ,
custom_form_id          ,
document_status         ,
transaction_type        ,
currency_id             ,
trandate                ,
exchange_rate           ,
account_id              ,
amount                  ,
amount_foreign          ,
gross_amount            ,
net_amount              ,
net_amount_foreign      ,
quantity                ,
item_id                 ,
item_unit_price         ,
tax_item_id             ,
tax_amount              ,
location_id             ,
class_id                ,
subsidiary_id           ,
accounting_period_id    ,
check_id                ,
check_type              ,
created_by_id           ,
create_date             ,
date_last_modified      ,
trx_type                ,
memo
FROM dw_prestage.check_fact a2
WHERE NOT EXISTS ( SELECT 1 FROM dw_prestage.check_fact_insert b2
WHERE b2.transaction_id = a2.transaction_id
AND b2.transaction_line_id = a2.transaction_line_id 
AND   a2.subsidiary_id = b2.subsidiary_id
)
MINUS
SELECT 
document_number         ,
transaction_number      ,
transaction_id          ,
transaction_line_id     ,
transaction_order       ,
custom_form_id          ,
document_status         ,
transaction_type        ,
currency_id             ,
trandate                ,
exchange_rate           ,
account_id              ,
amount                  ,
amount_foreign          ,
gross_amount            ,
net_amount              ,
net_amount_foreign      ,
quantity                ,
item_id                 ,
item_unit_price         ,
tax_item_id             ,
tax_amount              ,
location_id             ,
class_id                ,
subsidiary_id           ,
accounting_period_id    ,
check_id                ,
check_type              ,
created_by_id           ,
create_date             ,
date_last_modified      ,
trx_type                ,
memo
FROM dw_stage.check_fact a1
WHERE EXISTS ( SELECT 1 FROM dw_prestage.check_fact b1
WHERE b1.transaction_id = a1.transaction_id
AND b1.transaction_line_id = a1.transaction_line_id 
AND   a1.subsidiary_id = b1.subsidiary_id
)
)
) a
WHERE NOT EXISTS (SELECT 1
FROM dw_prestage.check_fact_insert aa
WHERE aa.transaction_id = a.transaction_id
AND  aa.transaction_line_id = a.transaction_line_id);

请建议替换此查询的任何替代方法。

Answer 1

此错误是因为NOT EXISTS子句中的查询引用了子句外部的表。将NOT EXISTS子句转换为带有谓词的LEFT JOIN以排除任何匹配项。此模式通常称为"反联接"。

当前：

FROM (…) a
WHERE NOT EXISTS (SELECT 1
FROM dw_prestage.check_fact_insert aa
WHERE aa.transaction_id = a.transaction_id
AND  aa.transaction_line_id = a.transaction_line_id)

重写：

FROM (…) a
LEFT JOIN dw_prestage.check_fact_insert aa
ON a.transaction_id = aa.transaction_id
AND a.transaction_line_id = aa.transaction_line_id
WHERE aa.transaction_id IS NULL