我如何转换“遗产"?Oracle中的左外连接语句? [英] How do I convert a "legacy" left outer join statement in Oracle?

问题描述

我在 Oracle 数据库中有两个表(A 和 G)，可以根据帐号将它们连接在一起.对此的一个警告是，其中一个表 (G) 的记录比另一个表少约 80 条.当我一起查询两个表时，我需要获取所有的行，以便我们在列中看到缺失的 80 行的 NULL 数据.

I have two tables (A and G) in an Oracle database that can be joined together based off an account number. The one caveat to this is that one of the tables (G) has about 80 fewer records than the other. When I query the two tables together, I need to get all of the rows, so that we see NULL data in the columns for the missing 80 rows.

我目前有一个使用以下传统"语法执行左外连接查询的 Oracle 语句:

I currently have an Oracle statement that performs a left outer join query using the following "legacy" syntax:

SELECT A.AccountNo,
       A.ParcelNo,
       A.LocalNo,
       A.PrimaryUseCode, 
       A.DefaultTaxDistrict,
       RTRIM(G.Section),
       RTRIM(G.Township),
       RTRIM(g.Range)

  FROM tblAcct A, tblAcctLegalLocation G

 WHERE A.verstart <= '20100917999' AND A.verend > '20100917999' AND A.DefaultTaxDistrict = '2291' 
       AND (SUBSTR(A.AccountNo,1,1) = 'R' or SUBSTR(A.AccountNo,1,1)= 'I') 
       AND SUBSTR(a.ParcelNo,1,1)<> '7' and substr(a.ParcelNo,1,1)<>'8'
       AND A.AcctStatusCode IN ('A', 'T', 'E') 
       AND A.AccountNo = G.AccountNo(+)
       AND G.verstart(+) <= '20100917999' and G.verend(+) > '20100917999'
ORDER BY A.ParcelNo, A.LocalNo

我正在尝试将此查询转换为标准"LEFT JOIN 类型查询，因为我听说较新版本的 Oracle 支持此语法.我已经尝试了基本的

I'm trying to convert this query into a "standard" LEFT JOIN type query since I'm told the newer versions of Oracle support this syntax. I've tried the basic

LEFT OUTER JOIN ON A.AccountNo = G.AccountNo 

但这似乎不起作用.我的查询最终返回的行数比全数少 80 行.

but this doesn't seem to work. My queries wind up returning 80 rows fewer than the full amount.

谁能告诉我我遗漏了什么或如何正确格式化查询?

Can anybody tell me what I'm missing or how to format the query properly?

推荐答案

使用:

  SELECT a.AccountNo,
         a.ParcelNo,
         a.LocalNo,
         a.PrimaryUseCode, 
         a.DefaultTaxDistrict,
         TRIM(g.Section),
         TRIM(g.Township),
         TRIM(g.Range)
     FROM tblAcct A
LEFT JOIN tblAcctLegalLocation g ON g.accountno = a.accountno
                                AND g.verstart <= '20100917999' 
                                AND g.verend > '20100917999'
    WHERE a.verstart <= '20100917999' 
      AND a.verend > '20100917999' 
      AND a.DefaultTaxDistrict = '2291' 
      AND SUBSTR(a.AccountNo,1,1) IN ('R', 'I') 
      AND SUBSTR(a.ParcelNo,1,1) NOT IN ('7', '8')
      AND a.AcctStatusCode IN ('A', 'T', 'E') 
 ORDER BY a.ParcelNo, a.LocalNo

您看到的所有标有 (+) 的内容都必须包含在 OUTER 连接条件中.在外部 JOIN 中，条件在连接之前应用.

Everything you see marked with the (+) must be included in the OUTER join criteria. In an outer JOIN, the criteria is applied before the join.

这篇关于我如何转换“遗产"?Oracle中的左外连接语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！