Total Pageviews

2015/10/16

[Oracle] Using "WITH AS" to Refactor SQL

As-Is
Assume I have a 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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
SELECT :startYear AS account_yr,
       budget_status AS budget_status,
       data_type AS data_type,
       sum(ALLC_AMOUNT) AS bondARest
FROM
  (SELECT lpad(to_char(a.ACCOUNT_YR, 'yyyy')-1911, 3, '0') AS ACCOUNT_YR,
          a.BUDGET_STATUS AS BUDGET_STATUS,
          a.BUDGET_CODE AS BUDGET_CODE,
          a.DEBT_CODE AS DEBT_CODE,
          a.DATA_TYPE AS DATA_TYPE,
          b.DEBT_TYPE AS DEBT_TYPE,
          b.BOND_TYPE AS BOND_TYPE,
          nvl(b.ALLC_AMOUNT, 0) AS ALLC_AMOUNT
   FROM
     (SELECT ACCOUNT_YR,
             BUDGET_CODE,
             DEBT_CODE,
             DATA_TYPE,
             BUDGET_STATUS,
             END_DATE,
             MAX(BUDGET_STATUS) OVER (PARTITION BY ACCOUNT_YR, BUDGET_CODE, DEBT_CODE, DATA_TYPE, SUBSTR(BUDGET_STATUS, 0, 1)) AS maxBudgetStatus
      FROM DBM091FA
      WHERE DATA_TYPE IN ('C',
                          'I')) a,
        DBM091FB b
   WHERE a.ACCOUNT_YR=b.ACCOUNT_YR
     AND a.BUDGET_CODE=b.BUDGET_CODE
     AND a.DEBT_CODE=b.DEBT_CODE
     AND a.DATA_TYPE=b.DATA_TYPE
     AND a.BUDGET_STATUS=b.BUDGET_STATUS
     AND a.BUDGET_STATUS=a.maxBudgetStatus
   ORDER BY a.ACCOUNT_YR,
            a.BUDGET_STATUS,
            a.BUDGET_CODE,
            a.DEBT_CODE)
WHERE budget_code=1
  AND debt_type=1
  AND bond_type=21
  AND account_yr BETWEEN '083' AND :startYear
GROUP BY budget_status,
         data_type
UNION
SELECT account_yr,
       budget_status,
       data_type,
       sum(ALLC_AMOUNT) bondARest
FROM
  (SELECT lpad(to_char(a.ACCOUNT_YR, 'yyyy')-1911, 3, '0') AS ACCOUNT_YR,
          a.BUDGET_STATUS AS BUDGET_STATUS,
          a.BUDGET_CODE AS BUDGET_CODE,
          a.DEBT_CODE AS DEBT_CODE,
          a.DATA_TYPE AS DATA_TYPE,
          b.DEBT_TYPE AS DEBT_TYPE,
          b.BOND_TYPE AS BOND_TYPE,
          nvl(b.ALLC_AMOUNT, 0) AS ALLC_AMOUNT
   FROM
     (SELECT ACCOUNT_YR,
             BUDGET_CODE,
             DEBT_CODE,
             DATA_TYPE,
             BUDGET_STATUS,
             END_DATE,
             MAX(BUDGET_STATUS) OVER (PARTITION BY ACCOUNT_YR, BUDGET_CODE, DEBT_CODE, DATA_TYPE, SUBSTR(BUDGET_STATUS, 0, 1)) AS maxBudgetStatus
      FROM DBM091FA
      WHERE DATA_TYPE IN ('C',
                          'I')) a,
        DBM091FB b
   WHERE a.ACCOUNT_YR=b.ACCOUNT_YR
     AND a.BUDGET_CODE=b.BUDGET_CODE
     AND a.DEBT_CODE=b.DEBT_CODE
     AND a.DATA_TYPE=b.DATA_TYPE
     AND a.BUDGET_STATUS=b.BUDGET_STATUS
     AND a.BUDGET_STATUS=a.maxBudgetStatus
   ORDER BY a.ACCOUNT_YR,
            a.BUDGET_STATUS,
            a.BUDGET_CODE,
            a.DEBT_CODE)
WHERE budget_code=1
  AND debt_type=1
  AND bond_type=21
  AND account_yr > :startYear
GROUP BY account_yr,
         budget_status,
         data_type
ORDER BY account_yr

You may notice the SQL statement which had been highlighted is duplicated.

To-be
You can utilize "with as..." to do SQL refactor.

 The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

The foregoing SQL statement will be amended 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
with predictedNumber as (
SELECT lpad(to_char(a.ACCOUNT_YR, 'yyyy')-1911, 3, '0') AS ACCOUNT_YR,
       a.BUDGET_STATUS AS BUDGET_STATUS,
       a.BUDGET_CODE AS BUDGET_CODE,
       a.DEBT_CODE AS DEBT_CODE,
       a.DATA_TYPE AS DATA_TYPE,
       b.DEBT_TYPE AS DEBT_TYPE,
       b.BOND_TYPE AS BOND_TYPE,
       nvl(b.ALLC_AMOUNT, 0) AS ALLC_AMOUNT
FROM
  (SELECT ACCOUNT_YR,
          BUDGET_CODE,
          DEBT_CODE,
          DATA_TYPE,
          BUDGET_STATUS,
          END_DATE,
          MAX(BUDGET_STATUS) OVER (PARTITION BY ACCOUNT_YR, BUDGET_CODE, DEBT_CODE, DATA_TYPE, SUBSTR(BUDGET_STATUS, 0, 1)) AS maxBudgetStatus
   FROM DBM091FA
   WHERE DATA_TYPE IN ('C',
                       'I')) a,
     DBM091FB b
WHERE a.ACCOUNT_YR=b.ACCOUNT_YR
  AND a.BUDGET_CODE=b.BUDGET_CODE
  AND a.DEBT_CODE=b.DEBT_CODE
  AND a.DATA_TYPE=b.DATA_TYPE
  AND a.BUDGET_STATUS=b.BUDGET_STATUS
  AND a.BUDGET_STATUS=a.maxBudgetStatus
ORDER BY a.ACCOUNT_YR,
         a.BUDGET_STATUS,
         a.BUDGET_CODE,
         a.DEBT_CODE
)
select :startYear AS account_yr, budget_status AS budget_status, data_type AS data_type, sum(ALLC_AMOUNT) AS bondARest
from
predictedNumber
where budget_code=1 and debt_type=1 and bond_type=21 and account_yr between '083' and :startYear
group by budget_status, data_type

union

select account_yr, budget_status, data_type, sum(ALLC_AMOUNT) bondARest
from
predictedNumber
where budget_code=1 and debt_type=1 and bond_type=21 and account_yr > :startYear
group by account_yr, budget_status, data_type
order by account_yr


Reference
[1] https://oracle-base.com/articles/misc/with-clause
Posted by Albert Kuo at 10:13 AM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)