sql - 物化视图中不支持 plsql 函数
问题描述
我想创建一个物化视图（快速刷新）。它包含一些复杂的计算，涉及大约 25 个以上的表。它在写在 where 子句中的 PLSQL 函数上给了我错误
MV 不支持 PLSQL 函数
下面是我正在尝试创建的 MV。如果我遗漏了一些东西，请修改语法，以便它可以允许联合和 PLSQL 函数快速刷新。
请注意，我的管理层不允许更改查询，因此请建议我解决方法以使用 MV 形式的相同查询。
create materialized view SAMPLE
ENABLE QUERY REWRITE
refresh fast
as
select ms.employee_id,
ms.sal_code code,
'SALARY' Account_type,
UPPER(sb.salary_breakup) Account_title,
sb.account_code ACCOUNT_CODE,
('ADDITION') AMOUNT_TYPE,
sb.pay_type PAY_TYPE,
p.display_title PAY_TITLE,
ms.salary AMOUNT,
p.order_in_payroll,
to_number((select order_by from payroll_account_title_org_wise
where org_id=p.org_id and account_id=1) || substr(sb.sal_code,3,3)) Code_order,
ms.month,
sb.tax_exemption,
sb.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
p.org_id
from PAY_MONTHLY_SALARY ms,pay_salary_breakups sb,PAYROL_TYPE p
where ms.sal_code=sb.sal_code
and p.pay_type=sb.pay_type
--order by sb.sal_code
union
-----ALLOWANCE PART
select mad.employee_id,
mad.ad_code code,
ad.type Account_type,
UPPER(ad.ad_name) Account_title,
ad.account_code ACCOUNT_CODE,
'ADDITION' AMOUNT_TYPE,
ad.pay_type PAY_TYPE,
p.display_title PAY_TITLE,
mad.amount AMOUNT,
p.order_in_payroll,
to_number((select order_by from payroll_account_title_org_wise
where org_id=p.org_id and account_id=2) || substr(mad.ad_code,3,3)) Code_order,
mad.month,
ad.tax_exemption,
ad.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
p.org_id
from Pay_Month_Allow_Ded mad,pay_allowance_deduction_setup ad,PAYROL_TYPE p
where mad.ad_code=ad.ad_code
and p.pay_type=ad.pay_type
and ad.type ='ALLOWANCE'
--order by ad.ad_code
union
-----DEDUCTION PART
select mad.employee_id,
mad.ad_code code,
ad.type Account_type,
UPPER(ad.ad_name) Account_title,
ad.account_code ACCOUNT_CODE,
'DEDUCTION' AMOUNT_TYPE,
ad.pay_type PAY_TYPE,
p.display_title PAY_TITLE,
mad.amount AMOUNT,
p.order_in_payroll,
to_number((select order_by from payroll_account_title_org_wise
where org_id=p.org_id and account_id=3) || substr(mad.ad_code,3,3)) Code_order,
mad.month,
ad.tax_exemption,
ad.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
p.org_id
from Pay_Month_Allow_Ded mad,pay_allowance_deduction_setup ad,PAYROL_TYPE p
where mad.ad_code=ad.ad_code
and p.pay_type=ad.pay_type
and ad.type='DEDUCTION'
--order by ad.ad_code
union
---- FUNDS AND CONTRIBUTION PART
select e.employee_id
,fcs.fc_code
,'FUNDS' Account_type
,UPPER(fct.fc_type_name) ACCOUNT_TITLE
,(case when fct.type='EMPLOYEE' then fcs.account_code_credit
when fct.type='EMPLOYER' then fcs.account_code
end) ACCOUNT_CODE
,(case when fct.type='EMPLOYEE' then 'DEDUCTION'
when fct.type='EMPLOYER' then 'OTHERTAX'
end) AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,r.amount AMOUNT
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=4) || substr(fcs.fc_code,3,3)) Code_order
,r.month
,(case when fcs.TAX_EXEMPTION='Y' then fcs.tax_exemption
when fcs.TAX_EXEMPTION='N' then fct.tax_exemption
end) TAX_EXEMPTION,
fcs.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
pt.org_id
from
hr_employee_info e
,fund_contribution_records r
,fund_contributions_type fct
,fund_contributions_setup fcs
,payrol_type pt
where e.employee_id=r.employee_id
and r.contribution_type_id=fct.contribution_type_id
and r.fc_code=fcs.fc_code
and fct.fc_code=fcs.fc_code
and fct.payroll_type=pt.pay_type
union
----- ATTENDACE DATA PART
select
pmt.employee_id
,att.att_code
,'ATTENDANCE' Account_type
,UPPER(att.attendace_type) ACCOUNT_TITLE
,NULL ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,pmt.attendance_days AMOUNT
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=5) || substr(att.att_code,3,3)) Code_order
,pmt.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from att_attendance_seup att
,pay_monthly_attendance pmt
,payrol_type pt
where att.att_code=pmt.att_code
and pt.pay_type=att.pay_type
and pt.org_id=att.org_id
/*union
---- TARGET DATA PART
select
pmt.employee_id
,pts.ta_code CODE
,UPPER(pts.target_name) ACCOUNT_TITLE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,pmt.value AMOUNT
,pt.order_in_payroll
,to_number(5 || substr(pts.ta_code,4,1)) Code_order
,pmt.month
,'Y' TAX_EXEMPTION,
pt.org_id
from
pay_monthly_targets pmt
,pay_target_setup pts
,payrol_type pt
where pts.ta_code=pmt.ta_code
and pt.pay_type=pts.pay_type*/
union
----LOAN DISBURSEMENT PART
select
pmld.employee_id
,plns.ln_code code
,'LOAN DISBURSEMENT' Account_type
,UPPER(plns.loan_name) ACCOUNT_TITLE
,plns.account_code ACCOUNT_CODE
,'ADDITION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,pmld.amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=6) || substr(plns.ln_code,3,3)) Code_order
,pmld.month
,'Y' tax_exemption
,plns.taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
PAY_LOAN_SETUP plns
,pay_loan_monthly_disbursement pmld
,payrol_type pt
where plns.ln_code=pmld.ln_code
and plns.pay_type_disburse=pt.pay_type
union
------- LOAN INSTALLMENTS PART
select
peld.employee_id
,plns.ln_code code
,'LOAN INSTALLMENT' Account_type
,UPPER(plns.loan_name || '(I)') ACCOUNT_TITLE
,plns.account_code_installment ACCOUNT_CODE
,'DEDUCTION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,plei.amount amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=7) || substr(plns.ln_code,3,3)) Code_order
,plei.month
,'Y' tax_exemption
,plns.taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
PAY_LOAN_SETUP plns
,PAY_EMP_LOAN_DISBURSEMENT peld
,PAY_EMP_LOAN_INSTALMENTS plei
,payrol_type pt
where plns.ln_code=peld.ln_code
and peld.loan_id=plei.loan_id
and plns.pay_type=pt.pay_type
and pt.org_id=plns.org_id
-----LOAN MARKUP
union
select
peld.employee_id
,plns.ln_code code
,'LOAN MARKUP' Account_type
,UPPER(plns.loan_name || '(M)') ACCOUNT_TITLE
,plns.account_code_markup ACCOUNT_CODE
,'DEDUCTION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,plei.benchmark_taken amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=8) || substr(plns.ln_code,3,3)) Code_order
,plei.month
,'Y' tax_exemption
,plns.taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
PAY_LOAN_SETUP plns
,PAY_EMP_LOAN_DISBURSEMENT peld
,PAY_EMP_LOAN_INSTALMENTS plei
,payrol_type pt
where plns.ln_code=peld.ln_code
and peld.loan_id=plei.loan_id
and plns.pay_type=pt.pay_type
--------REIMBURSEMENT PART
union
select
hrc.employee_id
,hrs.re_code CODE
,'REIMBURSEMENT' Account_type
,UPPER(hrs.reimbursement_name) ACCOUNT_TITLE
,hrs.account_code ACCOUNT_CODE
,'ADDITION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,hrc.amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=9) || substr(hrs.re_code,3,3)) Code_order
,hrc.month
,hrs.tax_exemption
,hrs.taxtation_type
,'Y' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
hr_reimbursement_setup hrs
,pay_monthly_reimbursement hrc
,payrol_type pt
,hr_employee_info e
where hrs.re_code=hrc.re_code
and hrc.employee_id=e.employee_id
and hrs.pay_type=pt.pay_type
--------OTHER INCOME TAX PART
union
select d.employee_id
,to_char('OT'||lpad(d.itax_other_id,3,000)) CODE
,'OTHER TAXABLE' Account_type
,UPPER(s.name || '(OT)') ACCOUNT_TITLE
,NULL ACCOUNT_CODE
,'OTHERTAX' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,(case when s.type='ADDITION' then i.amount
when s.type='EXEMPTION' then i.amount*-1
end) Amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=10) || lpad(d.itax_other_id,3,000)) CODE_ORDER
,i.month
,'N' TAX_EXEMPTION
,s.taxtation_type
,s.include_in_mothly_income INCLUDE_IN_MONTHLYTAX_INCOME
,i.org_id
from
PAY_MONTHLY_OTHER_TAX_INCOME i
,ITAX_DECLARE_OTHER_EMP_INCOME d
,ITAX_OTHER_INCOME_SETUP s
,payrol_type pt
where i.org_id=s.org_id
and d.org_id=s.org_id
and i.itax_declare_id=d.itax_declare_id
and s.itax_other_id=d.itax_other_id
and s.pay_type=pt.pay_type
and s.org_id=pt.org_id
-----TAXABLE INCOME(just to show)
union
select
i.employee_id
,t.tx_code CODE
,'TAXABLE INCOME' Account_type
,'TAXABLE INCOME' ACCOUNT_TITLE
,NULL ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
, sum(i.amount) amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=i.org_id and account_id=11) || substr(t.tx_code,3,3)) Code_order
,i.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,i.org_id
from
TAX_CODE_REGIONS t
,PAY_EMP_MONTHLY_TAXABLE_INCOME i
,payrol_type pt
where t.tx_code=i.tx_code
and i.INCLUDE_IN_MOTHLY_INCOME='Y'
--and t.pay_type=pt.pay_type
and pt.pay_type=Default_Value.GET_SYSTEM_DEFAULT('TXPAYTYPE',i.org_id)
group by i.employee_id,t.tx_code,pt.pay_type,pt.display_title,pt.order_in_payroll,i.month,i.org_id
--------Income Tax Part
union
select
i.employee_id
,t.tx_code CODE
,'INCOME TAX' Account_type
,'INCOME TAX' ACCOUNT_TITLE
,DEFAULT_VALUE.GET_SYSTEM_DEFAULT('ITAC',i.org_id) ACCOUNT_CODE
,'DEDUCTION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,i.amount amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=12) || substr(t.tx_code,3,3)) Code_order
,i.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,i.org_id
from
TAX_CODE_REGIONS t
,pay_monthly_itax i
,payrol_type pt
where t.tx_code=i.tx_code
--and t.pay_type=pt.pay_type
and pt.pay_type=Default_Value.GET_SYSTEM_DEFAULT('TXPAYTYPE',i.org_id)
-----GRATUITY PART(only for report purpose)
union
select
p.employee_id
,to_char('GP'||lpad(p.gratuity_id,3,000)) CODE
,'GRATUITY' Account_type
,UPPER(gs.name) ACCOUNT_TITLE
,gs.account_code ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,gs.pay_type
,pt.display_title PAY_TITLE
,p.amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=13) || lpad(p.gratuity_id,3,000)) CODE_ORDER
,p.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,pt.org_id
from
HR_GRATUITY_MONTHLY_ACCRUALS p,
HR_GRATUITY_SETUP gs,
payrol_type pt
where p.gratuity_id=gs.gratuity_id
and p.org_id=gs.org_id
and gs.pay_type=pt.pay_type
and p.org_id=pt.org_id
---------LEAVE ENCASHMENT PART
union
select
LED.Employee_Id
,'LE'||lpad(lt.leave_type_id,3,0) CODE
,'ALLOWANCE' ACCOUNT_TYPE
,UPPER(lt.leave_name) ACCOUNT_TITLE
,null ACCOUNT_CODE
,'ADDITION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,LED.Amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=2)||lpad(lt.leave_type_id,3,0)) CODE_ORDER
,LED.Month
,'N' TAX_EXEMPTION
,'VARIABLE' TAXTATION_TYPE
,'Y' INCLUDE_IN_MONTHLYTAX_INCOME
,LED.Org_Id
from
HR_LEAVE_ENCASHMENT_DISBRSE LED
,Hr_Leave_Type lt
,(select max(pt.order_in_payroll) order_in_payroll,pt.org_id from Payrol_Type pt group by pt.org_id) pt_a
,Payrol_Type pt
where LED.Leave_Type_Id=lt.leave_type_id
and LED.Org_Id=lt.org_id
and LED.Org_Id=pt_a.org_id
and pt_a.org_id=pt.org_id
and pt_a.order_in_payroll=pt.order_in_payroll
------Generate Fake Lines For every Employee against pay_type
union
select
e.employee_id
,'FAKE' CODE
,pt.display_title Account_type
,'FAKE' ACCOUNT_TITLE
,pt.account_code ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,0 AMOUNT
,pt.order_in_payroll
,99991 CODE_ORDER
,pi.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,pi.org_id
from
PAY_MONTHLY_PROCESS_INFO pi
,hr_employee_info e
,payrol_type pt
where pi.org_id=e.org_id
and pi.org_id=pt.org_id
and pi.month>=e.join_date;
解决方案
推荐阅读
- mysql - 根据 GROUP 和 LIMIT 获取唯一的每一行？
- python - Python中的字符数
- ajax - 在 Laravel 中提交模态框表单
- android - 如何在带有参数的android中设计惰性持有者模式？
- xamarin - 如何将 Xamarin Forms Checkbox isChecked 绑定到动态布尔变量？
- swift - “模棱两可的使用”和受约束的泛型函数
- javascript - CSS登录功能如何在页面上保持静止
- turbo-c++ - Turbo C++ 文件处理：卡在输入循环中
- excel - 在 Excel 中的不同工作表上匹配名字和姓氏
- r - 如果与另一列中的值匹配，则将列中的值替换为“空白”