Total Pageviews

2015/08/03

[Oracle to SQL Server Migration] Outer Join

In Oracle, we use outer join, (+), in the SQL statement as bellows:
SELECT A.MGE_TYPE,
       B.ORG_TYPE,
       F_DBM_FIND_CODE_NM('ORGTP', B.ORG_TYPE) AS ORG_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1_YEAR,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2_TEAR
FROM DBM034FA A,
     DBM030FA B,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY') = :year1) C,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY') = :year2) D
WHERE A.AGE = B.AGE
  AND A.STA_DATE = B.STA_DATE
  AND A.AGE = C.AGE(+)
  AND A.FUND_ID = C.FUND_ID(+)
  AND A.MGE_TYPE=C.MGE_TYPE(+)
  AND A.AGE = D.AGE(+)
  AND A.FUND_ID = D.FUND_ID(+)
  AND A.MGE_TYPE=D.MGE_TYPE(+)
  AND TO_CHAR(A.STA_DATE,'YYYY')=:year

Owing to (+) is the specific approach to do outer join in Oracle, it does not work in SQL Server.
Hence, the foregoing SQL statement should be translated as following:
SELECT A.MGE_TYPE,
       B.ORG_TYPE,
       AP_NTA.F_DBM_FIND_CODE_NM('ORGTP', B.ORG_TYPE) AS ORG_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1_YEAR,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2_TEAR
FROM DBM034FA A
INNER JOIN DBM030FA B ON A.AGE = B.AGE
AND A.STA_DATE = B.STA_DATE
AND CONVERT(VARCHAR(4), A.STA_DATE, 112)=:year
LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112) = :year1) C ON A.AGE = C.AGE
AND A.FUND_ID = C.FUND_ID
AND A.MGE_TYPE=C.MGE_TYPE
LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112) = :year2) D ON A.AGE = D.AGE
AND A.FUND_ID = D.FUND_ID
AND A.MGE_TYPE=D.MGE_TYPE

Reference
[1] https://technet.microsoft.com/zh-tw/library/ms187518(v=sql.105).aspx



No comments: