如何转换“旧版"广告素材?在Oracle中留下外部联接语句? [英] How do I convert a "legacy" left outer join statement in Oracle?

问题描述

我在Oracle数据库中有两个表(A和G)，可以根据帐号将它们连接在一起.一个需要注意的是，其中一个表(G)的记录少于另一个.当我一起查询两个表时，我需要获取所有行，以便我们在缺少的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

由于我被告知Oracle的较新版本支持此语法，因此我试图将此查询转换为标准" LEFT JOIN类型查询.我已经尝试过基本的

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屋！