替换 Oracle 外连接+运算符

Question

我需要重写几百个现有的Oracle SQL查询，其中一些使用+运算符进行外部连接。我是否正确转换了它们，我的错误是什么？

原始代码：

SELECT  DISTINCT 
:s1 || '^' || ll.street || '^' || tat.attribute_name    
FROM    
tsec ,
ll,
ta,
tat
WHERE   
tat.reference       LIKE 'ENGPYMNT%'
AND tat.system_name     = 'LAND'
AND ta.attribute_type_id        = tat.attribute_type_id 
AND ll.legal_id         = ta.source_id
AND tsec.program(+)     = 'TD_ATTRIBUTE_DETAILS'
AND tsec.item_name(+)       = 'ATTRIBUTES VIEW'
AND  tsec.system_name(+)     = 'LAND'
AND tsec.sql_user(+)        = :SqlUser
AND tsec.relation_type(+)       = 'ATTRIBUTE'
AND tsec.relation_id(+)     = tat.attribute_type_id
AND tsec.sec_level      > 0
AND LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
AND ll.street           LIKE UPPER( :s2 )
AND tat.attribute_name      LIKE :s3;

重写的代码：

select DISTINCT
:s1 || '^' || land_legal.street || '^' || td_attribute_types.attribute_name 
from
ta join tat on ta.attribute_type_id = tat.attribute_type_id 
join 
ll on ll.legal_id   = ta.source_id
left join 
tsec on tsec.relation_id    = tat.attribute_type_id
AND tsec.program    = 'TD_ATTRIBUTE_DETAILS'
AND tsec.item_name  = 'ATTRIBUTES VIEW'
AND tsec.system_name    = 'LAND'
AND tsec.sql_user   = :SqlUser
AND tsec.relation_type  = 'ATTRIBUTE'
where
tsec.sec_level      > 0
AND tat.reference       LIKE 'ENGPYMNT%'
AND LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
AND ll.street   LIKE UPPER( :s2 )
AND tat.attribute_name LIKE :s3
;

Answer 1

错误出在原始代码中(而不是在你翻译它)：

AND tsec.sec_level      > 0

不使用(+)关系，因此无法NULLtsec.sec_level，这有效地将联接从外部联接转换为内部联接。您对代码的翻译是"正确的"，但它使原始问题永久化，并且由于WHERE子句，SQL 引擎不会将LEFT JOIN作为LEFT JOIN实现，并且实际上将是一个INNER JOIN。

从旧逗号连接到现代 ANSI 连接语法的正确转换是：

SELECT DISTINCT 
:s1 || '^' || ll.street || '^' || tat.attribute_name    
FROM   ta
INNER JOIN tat
ON (ta.attribute_type_id = tat.attribute_type_id)
INNER JOIN ll
ON (ll.legal_id = ta.source_id)
INNER JOIN tsec
ON (tsec.relation_id = tat.attribute_type_id)
WHERE  tat.reference      LIKE 'ENGPYMNT%'
AND    tat.system_name    = 'LAND'
AND    tsec.program       = 'TD_ATTRIBUTE_DETAILS'
AND    tsec.item_name     = 'ATTRIBUTES VIEW'
AND    tsec.system_name   = 'LAND'
AND    tsec.sql_user      = :SqlUser
AND    tsec.relation_type = 'ATTRIBUTE'
AND    tsec.sec_level     > 0
AND    LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
AND    ll.street          LIKE UPPER( :s2 )
AND    tat.attribute_name LIKE :s3;

---

但是，如果原始代码是固定的，则行如下：

AND tsec.sec_level(+)      > 0

那么正确的代码将是：

SELECT DISTINCT 
:s1 || '^' || ll.street || '^' || tat.attribute_name    
FROM   ta
INNER JOIN tat
ON (ta.attribute_type_id = tat.attribute_type_id)
INNER JOIN ll
ON (ll.legal_id = ta.source_id)
LEFT OUTER JOIN tsec
ON (   tsec.relation_id   = tat.attribute_type_id
AND tsec.program       = 'TD_ATTRIBUTE_DETAILS'
AND tsec.item_name     = 'ATTRIBUTES VIEW'
AND tsec.system_name   = 'LAND'
AND tsec.sql_user      = :SqlUser
AND tsec.relation_type = 'ATTRIBUTE'
AND tsec.sec_level     > 0
)
WHERE  tat.reference      LIKE 'ENGPYMNT%'
AND    tat.system_name    = 'LAND'
AND    LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
AND    ll.street          LIKE UPPER( :s2 )
AND    tat.attribute_name LIKE :s3;