Oracle EBS SQL转Spark SQL：Exists子句跨域引用问题求助

转换思路 #

Spark SQL不支持相关子查询引用外部表，因此需要将原SQL中的EXISTS子查询和标量子查询替换为显式JOIN或预计算CTE（公共表表达式），同时将隐式JOIN改为显式JOIN以提升兼容性。

转换后的Spark SQL #

WITH core_charge_items AS (
    -- 预计算符合CORE CHARGE关系的物料ID，加上固定ID 23
    SELECT DISTINCT mri.inventory_item_id
    FROM apps.mtl_related_items mri
    JOIN apps.fnd_lookup_values flv
        ON mri.relationship_type_id = flv.lookup_code
    WHERE flv.lookup_type = 'MTL_RELATIONSHIP_TYPES'
      AND flv.language = 'US'
      AND flv.enabled_flag = 'Y'
      AND flv.end_date_active IS NULL
      AND UPPER(flv.meaning) = 'CORE CHARGE'
    UNION ALL
    SELECT 23 AS inventory_item_id
),
fulfillment_set_lines AS (
    -- 预计算属于FULFILLMENT_SET的订单行ID及对应订单头ID
    SELECT DISTINCT els.line_id, os.header_id
    FROM apps.oe_sets os
    JOIN apps.oe_line_sets els
        ON os.set_id = els.set_id
    WHERE os.set_type = 'FULFILLMENT_SET'
)
SELECT
    b.line_id,
    SUM(a.extended_amount) AS core_charge
FROM apps.ra_customer_trx_lines_all a
INNER JOIN apps.oe_order_lines_all b
    ON a.interface_line_attribute6 = b.line_id
INNER JOIN apps.oe_order_headers_all c
    ON b.header_id = c.header_id
INNER JOIN apps.ra_customer_trx_all d
    ON a.customer_trx_id = d.customer_trx_id
LEFT JOIN apps.oe_order_sources osource
    ON c.order_source_id = osource.order_source_id
LEFT JOIN core_charge_items cci
    ON b.inventory_item_id = cci.inventory_item_id
LEFT JOIN fulfillment_set_lines fsl
    ON a.interface_line_attribute6 = fsl.line_id
    AND c.header_id = fsl.header_id
WHERE
    -- 原CASE条件转换为直接逻辑判断
    (
        (b.line_category_code = 'ORDER' AND a.interface_line_attribute3 IS NOT NULL)
        OR b.line_category_code = 'RETURN'
    )
    AND a.interface_line_context = 'ORDER ENTRY'
    AND b.line_category_code IN ('ORDER', 'RETURN')
    -- 原OR条件转换：要么来源是CONVERSION，要么属于当前订单头的FULFILLMENT_SET
    AND (
        osource.name = 'CONVERSION'
        OR fsl.line_id IS NOT NULL
    )
    -- 原第二个EXISTS条件：物料匹配CORE CHARGE关系或等于23
    AND cci.inventory_item_id IS NOT NULL
GROUP BY b.line_id

关键转换说明 #

1. CASE条件简化：将原CASE表达式直接转为逻辑判断，避免Spark对复杂CASE的解析问题。
2. 标量子查询替换：原查询中获取订单来源名称的标量子查询，改为LEFT JOIN oe_order_sources表，直接判断osource.name = 'CONVERSION'。
3. 第一个EXISTS转换：通过CTE fulfillment_set_lines预计算所有属于FULFILLMENT_SET的订单行ID及对应订单头ID，主查询LEFT JOIN后通过fsl.line_id IS NOT NULL判断当前订单行是否属于对应订单头的FULFILLMENT_SET。
4. 第二个EXISTS转换：通过CTE core_charge_items预计算所有符合CORE CHARGE关系的物料ID，再加上固定ID 23，主查询LEFT JOIN后通过cci.inventory_item_id IS NOT NULL判断物料是否满足条件。
5. 隐式JOIN改显式：将原SQL中的逗号分隔隐式JOIN改为INNER JOIN，符合Spark SQL的最佳实践，同时提升代码可读性。

内容的提问来源于stack exchange，提问作者Milind Agarwal