How do I convert a “legacy” left outer join statement in Oracle?

by

Use:

  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

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.

More Related Contents:

  1. Oracle “(+)” Operator
  2. Oracle (Old?) Joins – A tool/script for conversion?
  3. How to create id with AUTO_INCREMENT on Oracle?
  4. LISTAGG in Oracle to return distinct values
  5. Fetch the row which has the Max value for a column
  6. Oracle SQL: Update a table with data from another table
  7. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  8. Fetch the rows which have the Max value for a column for each distinct value of another column
  9. SQL to generate a list of numbers from 1 to 100
  10. How to use BOOLEAN type in SELECT statement
  11. How to show all privileges from a user in oracle?
  12. SQL – How to select a row having a column with max value [duplicate]
  13. Oracle: loading a large xml file?
  14. IF EXISTS condition not working with PLSQL
  15. Using pivot on multiple columns of an Oracle row
  16. Check if current date is between two dates Oracle SQL
  17. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  18. Unordered results in SQL
  19. Oracle Dynamic Pivoting
  20. Foreign Key Creation issue in Oracle
  21. create custom function for date difference excluding weekends and holidays in oracle sql
  22. What is it exactly a BLOB in a DBMS context
  23. Custom Order in Oracle SQL
  24. Compare Strings ignoring accents in SQL (ORACLE)
  25. Return number from Oracle Select statement after parsing date
  26. How do you interpret a query’s explain plan?
  27. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
  28. How to call Oracle MD5 hash function?
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment