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:
Post a Comment