Oracle retrieves data from each table and saves it to another table.
Obtain data from each table and save it to another table:
CREATE VIEW
PARAMETER_view
WITH
Tall
(
SELECT
P. PI_NO, -- product serial number
P. SERIALNO, -- product no.
P. PI_NAME, -- product name
P. PI_START_DATE, -- product start date
P. PI_END_DATE, -- product period Date
P. PI_CUSTOMER_YIELD/100 PI_CUSTOMER_YIELD, -- product yield
A. AI_NO, -- asset serial number
A. CAS_ASSETS_NA, -- asset name
A. DONO, -- asset code
A. CAS_ISSUE_SUM, -- Asset Release amount
A. CAS_YIELD_RATE/100 CAS_YIELD_RATE, -- asset return rate
A. CAS_VALUE_DATE, -- asset start date
A. CAS_DUE_DATE, -- asset expiration date
NVL (FeeNum, 0) FeeNum, -- other fees
NVL (FeeRate, 0) FeeRate, -- Other rate
NVL (fm1_, 0) fm1_, -- accumulative floating Management Fee
NVL (fmfee3/100,0) fmfee3, -- floating Management Fee
NVL (ct. FEERATIO/, 0) FEERATIO, -- product license fee
NVL (cg. cgrate/100,0) cgrate, -- product management fee
NVL (atrate/, 0) atrate, -- asset renewal fee
NVL (agrate/) agrate, -- Asset Management Fee
NVL (cgrates/) cgrates, -- product investment fee
NVL (agrates/) agrates, -- Asset Investment fee
Gl_date, -- Transaction date
Floor (SYSDATE-to_date (PI_START_DATE, 'yyyy-mm-dd hh24: mi: ss') pflday, -- product accumulative days
Floor (SYSDATE-to_date (CAS_VALUE_DATE, 'yyyy-mm-dd hh24: mi: ss') aflday -- asset accumulation days
FROM
GL_PRODUCTASSETS g
LEFT JOIN
Product_info p
ON
G. PBS_MAINPRO_NO = p. PI_NO
LEFT JOIN
ASSETS_INFO
ON
G. CAS_ASSETS_NU = a. AI_NO
LEFT JOIN
(
SELECT
Productid,
SUM (fm1_) fm1 _
FROM
Product_fm1 _
GROUP
Productid) f
ON
G. PBS_MAINPRO_NO = f. PRODUCTID
LEFT JOIN
(
SELECT
FEERATIO fmfee3,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
FEETYPE = 'floating management') f2
ON
P. PI_NO = f2.FEEOBJECTNO
LEFT JOIN
(
SELECT
*
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'product'
And feetype = 'balance Bill') ct
ON
P. PI_NO = ct. FEEOBJECTNO
LEFT JOIN
(
SELECT
Sum (FEERATIO) cgrate,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'product'
And feetype = 'manage' 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 = 'Investment expense ') cgg
ON
P. PI_NO = cgg. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO atrate,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'asset'
And feetype = 'balance payby') att
ON
A. AI_NO = att. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrate,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'asset'
And feetype = 'manage') ag
ON
A. AI_NO = ag. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrates,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'asset'
And feetype = 'pay-as-you-go ') Renew
ON
A. AI_NO = response. FEEOBJECTNO
LEFT JOIN
(
SELECT
SUM (FEERATIO) FeeNum,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'product'
And feetype not in ('balance Bill ',
'Investment expense ',
'Management management ',
'Floating management ')
And feeratio> 1
GROUP
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 ('balance Bill ',
'Investment expense ',
'Management management ',
'Floating management ')
And feeratio <1
GROUP
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, -- product number, product name, and product daily income
PI_CUSTOMER_YIELD * CAS_ISSUE_SUM/365 * pflday p_all_receive, -- the accumulation FROM THE DAY OF INTEREST TO THE DAY
Cgrate * CAS_ISSUE_SUM/365 p_day_manage, -- daily product management fee
Cgrate * CAS_ISSUE_SUM/365 * pflday p_all_manage, -- total product management fee
FEERATIO * CAS_ISSUE_SUM/365 p_day_tm, -- the product's daily renewal fee
FEERATIO * CAS_ISSUE_SUM/365 * pflday p_all_tm, -- total product renewal fee
CAS_ASSETS_NA, CAS_YIELD_RATE * CAS_ISSUE_SUM/365 a_day_receive, -- asset ID, asset name, and daily income of assets
CAS_YIELD_RATE * CAS_ISSUE_SUM/365 * aflday a_all_receieve, -- the accumulation of the asset's interest from day to day
Agrate * CAS_ISSUE_SUM/365 a_day_manage, -- daily Asset Management Fee
Agrate * CAS_ISSUE_SUM/365 * aflday a_all_manage, -- total product management fee
Atrate * CAS_ISSUE_SUM/365 a_day_tm, -- daily asset renewal fee
Atrate * CAS_ISSUE_SUM/365 * aflday a_all_tm, -- total asset renewal fee
'0' tdbid,
'0' tdallfee,
Fmfee3,
Cgrates * CAS_ISSUE_SUM/365 p_day_tes, -- pay-as-you-go for the product on the same day
Cgrates * CAS_ISSUE_SUM/365 * pflday p_all_tes, -- total product investment fee
Agrates * CAS_ISSUE_SUM/365 a_day_tes, -- the same-day Asset Investment fee
Agrates * CAS_ISSUE_SUM/365 * aflday a_all_tes, -- total asset investment fee
FeeNum -- other fees
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
ON
G. CAS_ASSETS_NU = a. AI_NO
LEFT JOIN
(
SELECT
Productid,
SUM (fm1_) fm1 _
FROM
Product_fm1 _
GROUP
Productid) f
ON
G. PBS_MAINPRO_NO = f. PRODUCTID
LEFT JOIN
(
SELECT
Productid,
SUM (fm1_) fmfee3
FROM
Product_fm1 _
WHERE
INPUTDATE = TO_CHAR (SYSDATE, 'yyyy/MM/dd ')
GROUP
Productid) f2
ON
G. PBS_MAINPRO_NO = f2.PRODUCTID
LEFT JOIN
(
SELECT
*
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'product'
And feetype = 'balance Bill') ct
ON
P. PI_NO = ct. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO cgrate,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'product'
And feetype = 'Manage management') cg
ON
P. PI_NO = cg. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO cgrates,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'product'
And feetype = 'Investment expense ') cgg
ON
P. PI_NO = cg. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO atrate,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'asset'
And feetype = 'balance payby') att
ON
A. AI_NO = att. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrate,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'asset'
And feetype = 'manage') ag
ON
A. AI_NO = ag. FEEOBJECTNO
LEFT JOIN
(
SELECT
FEERATIO agrates,
FEEOBJECTNO
FROM
PRODUCT_COST_INFO
WHERE
Feeobjecttype = 'asset'
And feetype = 'pay-as-you-go ') Renew
ON
A. AI_NO = ag. FEEOBJECTNO
WHERE
G. GL_STATE = 1
Then save the View data to the table you want to save:
Insert into (column name corresponding to the column in the view) Table A (select * from view chart)