select a.* from (select t.c_fund_account_name as "fundAccountNo", tfp.project_code as "projectCode", tfp.project_name as "projectName", tfp.project_shortname as "projectShortName", c.c_fund_name as "fundName", c.c_fund_code as "fundCode", nvl(thold.subsistAssetsShare, 0) as "subsisAssetsShare", to_char(thold.updateDate, 'yyyy-MM-dd') as "updateDate", nvl(c.c_current_share, 0) as "currentShare", to_char(c.d_date, 'yyyy-MM-dd') as "dateDate", (nvl(thold.subsistAssetsShare, 0) - nvl(c.c_current_share, 0)) as "diffValue", CAST((CASE WHEN (nvl(thold.subsistAssetsShare, 0) - nvl(c.c_current_share, 0)) = 0 THEN '1' WHEN (nvl(thold.subsistAssetsShare, 0) - nvl(c.c_current_share, 0)) <> 0 THEN '0' END) as nvarchar2(2)) as "identical" from t_fund_account t inner join (select fhs.*, row_number() over(partition by fhs.c_fund_account_no, fhs.c_project_code order by fhs.d_date desc) rn from td_fund_holding_share fhs) c on t.c_fund_account_name = c.c_fund_account_no and t.c_project_code = c.c_project_code LEFT JOIN (SELECT tha.project_code as projectCode, sum(tha.current_share) as subsistAssetsShare, sum(tha.current_cost) as currentCost, sum(tha.accumulated_profit) as accumulatedProfit, max(tha.update_time) as updateDate FROM t_hold_assets tha left join t_polling_product p on tha.c_product_code = p.c_product_code WHERE 1 = 1 and tha.delete_flag = '0' and p.c_stock_type_level1 = '0' and p.c_stock_type_level2 = '01' GROUP BY tha.project_code) thold on thold.projectCode = t.c_project_code left join t_family_project tfp on tfp.project_code = t.c_project_code and tfp.delete_flag = '0' where rn = 1 AND t.c_fund_account_type = '1' AND t.delete_flag = '0' UNION ALL select t.c_fund_account_name as "fundAccountNo", tfp.project_code as "projectCode", tfp.project_name as "projectName", tfp.project_shortname as "projectShortName", CAST('' as nvarchar2(50)) as "fundName", CAST('' as nvarchar2(50)) as "fundCode", nvl(thold.subsistAssetsShare, 0) as "subsisAssetsShare", to_char(thold.updateDate, 'yyyy-MM-dd') as "updateDate", to_number(nvl('', 0)) as "currentShare", to_char(CAST('' as nvarchar2(50)), 'yyyy-MM-dd') as "dateDate", nvl(thold.subsistAssetsShare, 0) - nvl('', 0) as "diffValue", CAST((CASE WHEN (nvl(thold.subsistAssetsShare, 0) - nvl('', 0)) = 0 THEN '1' WHEN (nvl(thold.subsistAssetsShare, 0) - nvl('', 0)) <> 0 THEN '0' END) as nvarchar2(2)) as "identical" from t_fund_account t LEFT JOIN (SELECT tha.project_code as projectCode, sum(tha.current_share) as subsistAssetsShare, max(tha.update_time) as updateDate FROM t_hold_assets tha left join t_polling_product p on tha.c_product_code = p.c_product_code WHERE 1 = 1 and tha.delete_flag = '0' and p.c_stock_type_level1 = '0' and p.c_stock_type_level2 = '01' GROUP BY tha.project_code) thold on thold.projectCode = t.c_project_code left join t_family_project tfp on t.c_project_code = tfp.project_code and tfp.delete_flag = '0' where t.c_fund_account_name not in (select td.c_fund_account_no from td_fund_holding_share td) and t.c_fund_account_type = '1' and t.delete_flag = '0') a order by a."diffValue" desc, a."projectCode" desc
|