amazon-redshift - 错误消息是 [Amazon](500310) Invalid operation: This type of associated subquery pattern is not supported due to internal error
问题描述
这是查询
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);
请建议任何替代方法来替换此查询。
解决方案
该错误是因为您的NOT EXISTS
子句中的查询引用了该子句之外的表。将NOT EXISTS
子句转换为LEFT JOIN
带有谓词的 a 以排除任何匹配项。这种模式通常被称为“反连接”。
当前的:
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
推荐阅读
- kubernetes - 带有 EKS 的 Terraform Kubernetes 提供程序在 configmap 上失败
- api - 用于租户隔离的 API 流量整形/节流策略
- python - 张量不是该图的元素;部署 Keras 模型
- html - 按钮仅部分可点击错误
- ruby-on-rails - 自动化在 AWS 上部署 Ember-CLI-Rails 应用程序的任务
- jquery - jQuery:获取图像高度+如果需要添加填充
- react-native - 降低抽屉打开速度
- c# - 如何将自定义 ConfigurationSection 添加到程序集?
- typescript - 无法理解 Angular 如何将 json 映射到对象
- google-bigquery - bq load:“加载操作中的 BigQuery 错误:未找到:项目 ...”