回复 3# richard_macy erge into FDM_PA_AC DES using (select SRC1.ACCOUNT_NUMBER, SRC1.AS_OF_DATE, SRC1.BUSS_LINE_ID,
SRC1.COMMON_COA_ID, SRC1.DATA_SOURCE_CD,
SRC1.GL_ACCOUNT_ID, SRC1.IDENTITY_CODE,
SRC1.ISO_CURRENCY_CD, SRC1.ORG_UNIT_ID, SRC1.RM_COA_ID,
SRC1.TP_BUSS_ID, SRC1.UP_ORG_UNIT_ID, SRC1.DEPARTMENT_ID,
SRC1.PRODUCT_ID, SRC1.CUST_MNGR_ID, sum(
SRC3.SUM_ALLOC_BASE * coalesce(SRC1.AVG_BOOK_BAL, 0)/
SRC2.SUM_PERCENT_BASE)as ALLOC_RESULT
from FDM_PA_AC SRC1 left outer join
(select DEPARTMENT_ID, PRODUCT_ID, sum(coalesce(AVG_BOOK_BAL,
0))as SUM_PERCENT_BASE
from FDM_PA_AC
where AVG_BOOK_BAL > 0
group by DEPARTMENT_ID, PRODUCT_ID)SRC2 on SRC1.DEPARTMENT_ID
=SRC2.DEPARTMENT_ID and SRC1.PRODUCT_ID =SRC2.PRODUCT_ID
left outer join
(select DEPARTMENT_ID, PRODUCT_ID, sum(coalesce(
CURRENT_AMOUNT, 0)* 0.4)as SUM_ALLOC_BASE
from FDM_PA_GL
where CURRENT_AMOUNT !=0 and FINANCIAL_ELEM_ID =632 and
(IDENTITY_CODE in (321, 323))and FEE_TYPE =1 and
(GL_ACCOUNT_ID not in ('6601011901' , '6601011902' ,
'6601011999' ))and AS_OF_DATE ='20150131'
group by DEPARTMENT_ID, PRODUCT_ID)SRC3 on SRC1.DEPARTMENT_ID
=SRC3.DEPARTMENT_ID and SRC1.PRODUCT_ID =SRC3.PRODUCT_ID
where SRC1.AVG_BOOK_BAL > 0 and SRC2.SUM_PERCENT_BASE !=0 and
SRC2.SUM_PERCENT_BASE is not null and
SRC3.SUM_ALLOC_BASE is not null
group by SRC1.ACCOUNT_NUMBER, SRC1.AS_OF_DATE, SRC1.BUSS_LINE_ID,
SRC1.COMMON_COA_ID, SRC1.DATA_SOURCE_CD,
SRC1.GL_ACCOUNT_ID, SRC1.IDENTITY_CODE,
SRC1.ISO_CURRENCY_CD, SRC1.ORG_UNIT_ID, SRC1.RM_COA_ID,
SRC1.TP_BUSS_ID, SRC1.UP_ORG_UNIT_ID, SRC1.DEPARTMENT_ID,
SRC1.PRODUCT_ID, SRC1.CUST_MNGR_ID)SRC on
(DES.ACCOUNT_NUMBER =SRC.ACCOUNT_NUMBER and
DES.GL_ACCOUNT_ID =SRC.GL_ACCOUNT_ID and DES.IDENTITY_CODE =
SRC.IDENTITY_CODE and DES.DEPARTMENT_ID =SRC.DEPARTMENT_ID
and SRC.CUST_MNGR_ID =DES.CUST_MNGR_ID)
when matched
then
update set PA_FEE_ADMIN_EMPLOYEE =coalesce(PA_FEE_ADMIN_EMPLOYEE, 0)
+ coalesce(SRC.ALLOC_RESULT, 0)
when not matched
then
insert (ACCOUNT_NUMBER, AS_OF_DATE, BUSS_LINE_ID, COMMON_COA_ID,
DATA_SOURCE_CD, GL_ACCOUNT_ID, IDENTITY_CODE,
ISO_CURRENCY_CD, ORG_UNIT_ID, RM_COA_ID, TP_BUSS_ID,
UP_ORG_UNIT_ID, DEPARTMENT_ID, PRODUCT_ID, CUST_MNGR_ID,
PA_FEE_ADMIN_EMPLOYEE)values (SRC.ACCOUNT_NUMBER,
SRC.AS_OF_DATE, SRC.BUSS_LINE_ID, SRC.COMMON_COA_ID,
SRC.DATA_SOURCE_CD, SRC.GL_ACCOUNT_ID, SRC.IDENTITY_CODE,
SRC.ISO_CURRENCY_CD, SRC.ORG_UNIT_ID, SRC.RM_COA_ID,
SRC.TP_BUSS_ID, SRC.UP_ORG_UNIT_ID, SRC.DEPARTMENT_ID,
SRC.PRODUCT_ID, SRC.CUST_MNGR_ID, 0 + coalesce(
SRC.ALLOC_RESULT, 0))
ows
Operator
(ID)
Cost
51694.8
INSERT
( 2)
1.02651e+06
/ \
51694.8 2.26548e+06
TBSCAN Table:
( 3) RFMA
970657 FDM_PA_AC
|
51694.8
TEMP
( 4)
970611
|
51694.8
UPDATE
( 5)
970587
/---/ \
51694.8 2.26548e+06
FETCH Table:
( 6) RFMA
579606 FDM_PA_AC
/--/ \
51694.8 2.26548e+06
NLJOIN Table:
( 7) RFMA
188599 FDM_PA_AC
/-/ \
646185 0.08
FILTER UNION
( 8) (29)
187357 0.00173938
| / \
646185 0.04 0.04
TBSCAN FILTER FILTER
( 9) (30) (32)
186258 0.000692988 0.000692988
| | |
646185 1 1
SORT TBSCAN TBSCAN
(10) (31) (33)
186197 4.58174e-05 4.58174e-05
| | |
646185 TFunc: TFunc:
HSJOIN SYSIBM SYSIBM
(11) GENROW GENROW
185259
/----/ \-------\
2.26548e+06 646185
TBSCAN TBSCAN
(12) (13)
173128 11138.4
| |
2.26548e+06 646185
Table: TEMP
RFMA (14)
FDM_PA_AC 10562.3
|
646185
GRPBY
(15)
10156.4
|
646185
HSJOIN
(16)
10094.7
/-----/ \----\
701156 238.694
NLJOIN FILTER
(17) (26)
6426.21 3549.91
/ \ |
2142.52 327.258 259
FILTER IXSCAN GRPBY
(18) (25) (27)
103.346 40.2143 3549.75
| | |
2231.79 2.26548e+06 2.19525e+06
GRPBY Index: IXSCAN
(19) RFMA (28)
102.588 IDX509100649080000 3340.21
| |
2231.79 516082
TBSCAN Index:
(20) RFMA
102.375 IDX509100646550000
|
2231.79
SORT
(21)
102.161
|
2231.79
NLJOIN
(22)
99.9131
/ \
2 1115.89
TBSCAN IXSCAN
(23) (24)
5.72717e-05 49.964
| |
TFunc: 82998
SYSIBM Index:
GENROW RFMA
IDX509100648570000
收起