Total Pageviews

2015/10/12

[Oracle to SQL Server Migration] Outer Join

In Oracle, we use outer join, (+), in the SQL statement as bellows:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT TO_CHAR(A.STA_DATE,'YYYYMMDD') AS STA_DATE,
       A.AGE,
       A.FUND_ID,
       A.MGE_TYPE,
       F_DBM_FIND_LAGE_NM(A.STA_DATE,A.AGE) AGE_NAME,
       F_DBM_FIND_FUND_NM(A.STA_DATE,A.AGE,A.FUND_ID) FUND_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE0,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2,
       (E.IN_AMT/E.OUT_AMT) * 100 AS RATE3
FROM DBM034FA A,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1910) C,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1909) D,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1908) E
WHERE TO_CHAR(A.STA_DATE,'YYYY')=:accountYr +1911
  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 A.AGE = E.AGE(+)
  AND A.FUND_ID = E.FUND_ID(+)
  AND A.MGE_TYPE=E.MGE_TYPE(+)


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:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
select t.STA_DATE as STA_DATE, 
       t.AGE, 
       t.FUND_ID, 
       t.MGE_TYPE, 
       t.AGE_NAME, 
       t.FUND_NAME, 
       t.RATE0, 
       t.RATE1, 
       t.RATE2, 
       t.RATE3
from
(
SELECT CONVERT(VARCHAR(4), A.STA_DATE, 112),
       A.AGE,
       A.FUND_ID,
       A.MGE_TYPE,
       AP_NTA.F_DBM_FIND_LAGE_NM(A.STA_DATE,A.AGE) AGE_NAME,
       AP_NTA.F_DBM_FIND_FUND_NM(A.STA_DATE,A.AGE,A.FUND_ID) FUND_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE0,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2,
       (E.IN_AMT/E.OUT_AMT) * 100 AS RATE3
FROM DBM034FA A

LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112)=:accountYr +1910) 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)=:accountYr +1909) D
ON
  A.AGE = D.AGE
  AND A.FUND_ID = D.FUND_ID
  AND A.MGE_TYPE=D.MGE_TYPE

LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112)=:accountYr +1908) E
ON   
  A.AGE = E.AGE
  AND A.FUND_ID = E.FUND_ID
  AND A.MGE_TYPE=E.MGE_TYPE
 ) t
 where t.STA_DATE=:accountYr +1911




Posted by Albert Kuo at 2:14 PM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)