Oracle retrieves data from each table and saves it to another table.

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)