oracle從各個表取得資料儲存到另一個表

標籤：

從各個表中取得資料儲存另一個表中：

CREATE VIEW
PARAMETER_view AS
WITH
tall AS
(
SELECT
p.PI_NO,--產品序號
p.SERIALNO,--產品編號
p.PI_NAME,--產品名稱
p. PI_START_DATE,--產品起息日
p.PI_END_DATE,--產品期日期
p.PI_CUSTOMER_YIELD/100 PI_CUSTOMER_YIELD, --產品收益率
a.AI_NO, --資產序號
a.CAS_ASSETS_NA,--資產的名稱
a.DONO,--資產的編碼
a.CAS_ISSUE_SUM,--資產發行金額
a.CAS_YIELD_RATE/100 CAS_YIELD_RATE, --資產收益率
a.CAS_VALUE_DATE , --資產起息日
a.CAS_DUE_DATE , --資產到期日
NVL(FeeNum,0) FeeNum,--其他費用
NVL(FeeRate,0) FeeRate,--其他費率
NVL(fmfee,0) fmfee, --累計浮動管理費
NVL( fmfee3/100,0) fmfee3, --浮動管理費
NVL(ct.FEERATIO/100,0) FEERATIO, --產品的託管費
NVL( cg.cgrate/100,0) cgrate ， --產品的管理費
NVL( atrate/100,0) atrate，--資產的託管費
NVL( agrate/100,0) agrate ,--資產的管理費
NVL( cgrates/100,0) cgrates ,--產品的投顧費
NVL( agrates/100,0) agrates ,--資產的投顧費
gl_date， --交易日期
floor(SYSDATE-to_date(PI_START_DATE,‘yyyy-mm-dd hh24:mi:ss‘)) pflday，--產品累加天數
floor(SYSDATE-to_date(CAS_VALUE_DATE,‘yyyy-mm-dd hh24:mi:ss‘)) aflday--資產累加天數
FROM
GL_PRODUCTASSETS g
LEFT JOIN
Product_info p
ON
g.PBS_MAINPRO_NO=p.PI_NO
LEFT JOIN
ASSETS_INFO a
ON
g.CAS_ASSETS_NU=a.AI_NO
LEFT JOIN
(
SELECT
productid,
SUM(FMFEE) fmfee
FROM
PRODUCT_FMFEE
GROUP BY
productid ) f
ON
g.PBS_MAINPRO_NO=f.PRODUCTID
LEFT JOIN
(
SELECT
FEERATIO fmfee3,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
FEETYPE=‘浮動管理費‘ ) f2
ON
p.PI_NO=f2.FEEOBJECTNO
LEFT JOIN
(
SELECT
*
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE=‘託管費‘) ct
ON
p.PI_NO=ct.FEEOBJECTNO
LEFT JOIN
(
SELECT
sum( FEERATIO) cgrate ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE=‘管理費‘ group by FEEOBJECTNO ) cg
ON
p.PI_NO=cg.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO cgrates ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE=‘投顧費‘) cgg
ON
p.PI_NO=cgg.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO atrate ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Asset‘
AND FEETYPE=‘託管費‘) att
ON
a.AI_NO=att.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrate ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Asset‘
AND FEETYPE=‘管理費‘) ag
ON
a.AI_NO=ag.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrates ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Asset‘
AND FEETYPE=‘投顧費‘) agg
ON
a.AI_NO=agg.FEEOBJECTNO
LEFT JOIN
(
SELECT

SUM( FEERATIO ) FeeNum,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE NOT IN (‘託管費‘,
‘投顧費‘,
‘管理費‘,
‘浮動管理費‘)
AND FEERATIO>1
GROUP BY
FEEOBJECTNO
) fs
ON
a.AI_NO=fs.FEEOBJECTNO

LEFT JOIN
(
SELECT

SUM( FEERATIO ) FeeRate,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE NOT IN (‘託管費‘,
‘投顧費‘,
‘管理費‘,
‘浮動管理費‘)
AND FEERATIO<1
GROUP BY
FEEOBJECTNO
) fss
ON
a.AI_NO=fss.FEEOBJECTNO

WHERE
G.GL_STATE=1
)
SELECT
ta.*,
(a_day_receive+p_day_manage+p_day_tm+p_day_tes-p_day_receive-a_day_manage-a_day_tm-a_day_tes+
fmfee3+FeeNum) all_get　
FROM
(
SELECT
PI_NO,
PI_NAME,
PI_CUSTOMER_YIELD*CAS_ISSUE_SUM/365 p_day_receive， --產品編號，產品名稱，產品每日收益
PI_CUSTOMER_YIELD*CAS_ISSUE_SUM/365*pflday p_all_receive, --起息日到當天的累計
cgrate*CAS_ISSUE_SUM/365 p_day_manage， --產品當天管理費
cgrate*CAS_ISSUE_SUM/365*pflday p_all_manage， --產品累計管理費
FEERATIO*CAS_ISSUE_SUM/365 p_day_tm， --產品當天託管費
FEERATIO*CAS_ISSUE_SUM/365*pflday p_all_tm， --產品累計託管費
CAS_ASSETS_NA，CAS_YIELD_RATE*CAS_ISSUE_SUM/365 a_day_receive， --資產編號，資產名稱，資產每日收益
CAS_YIELD_RATE*CAS_ISSUE_SUM/365*aflday a_all_receieve, --資產起息日到當天的累計
agrate*CAS_ISSUE_SUM/365 a_day_manage， --資產當天管理費
agrate*CAS_ISSUE_SUM/365*aflday a_all_manage， --產品累計管理費
atrate*CAS_ISSUE_SUM/365 a_day_tm， --資產當天託管費
atrate*CAS_ISSUE_SUM/365*aflday a_all_tm, --資產累計託管費
‘0‘ tdfee,
‘0‘ tdallfee,
fmfee3,
cgrates*CAS_ISSUE_SUM/365 p_day_tes,--產品當天投顧費
cgrates*CAS_ISSUE_SUM/365*pflday p_all_tes,--產品累計投顧費
agrates*CAS_ISSUE_SUM/365 a_day_tes，--資產當天投顧費
agrates*CAS_ISSUE_SUM/365*aflday a_all_tes, --資產累計投顧費
FeeNum --其他費用
FROM
tall) ta ;





SELECT
DECODE(gl_date,a.CAS_VALUE_DATE, p.PI_NO , p.PI_NAME )
FROM
GL_PRODUCTASSETS g
LEFT JOIN
Product_info p
ON
g.PBS_MAINPRO_NO=p.PI_NO
LEFT JOIN
ASSETS_INFO a
ON
g.CAS_ASSETS_NU=a.AI_NO
LEFT JOIN
(
SELECT
productid,
SUM(FMFEE) fmfee
FROM
PRODUCT_FMFEE
GROUP BY
productid ) f
ON
g.PBS_MAINPRO_NO=f.PRODUCTID
LEFT JOIN
(
SELECT
productid,
SUM(FMFEE) fmfee3
FROM
PRODUCT_FMFEE
WHERE
INPUTDATE = TO_CHAR(SYSDATE,‘YYYY/MM/DD‘)
GROUP BY
productid) f2
ON
g.PBS_MAINPRO_NO=f2.PRODUCTID
LEFT JOIN
(
SELECT
*
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE=‘託管費‘) ct
ON
p.PI_NO=ct.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO cgrate ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE=‘管理費‘) cg
ON
p.PI_NO=cg.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO cgrates ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Product‘
AND FEETYPE=‘投顧費‘) cgg
ON
p.PI_NO=cg.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO atrate ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Asset‘
AND FEETYPE=‘託管費‘) att
ON
a.AI_NO=att.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrate ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Asset‘
AND FEETYPE=‘管理費‘) ag
ON
a.AI_NO=ag.FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrates ,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
feeobjecttype=‘Asset‘
AND FEETYPE=‘投顧費‘) agg
ON
a.AI_NO=ag.FEEOBJECTNO
WHERE
G.GL_STATE=1

 

 

然後把這個視圖資料儲存到你要儲存的表：

 

insert into (和視圖的列要對應的列名)表A（select *  from 視圖表） 

oracle從各個表取得資料儲存到另一個表