create view test3 as
select decode(sign(E.B),-1,0,
decode(sign(E.B-500),-1,E.B*0.05,
decode(sign(E.B-2000),-1,E.B*0.1-25,
decode(sign(E.B-5000),-1,E.B*0.15-125,
decode(sign(E.B-20000),-1,E.B*0.20-375,
decode(sign(E.B-40000),-1,E.B*0.25-1375,
decode(sign(E.B-60000),-1,E.B*0.30-3375,
decode(sign(E.B-80000),-1,E.B*0.35-6375,
decode(sign(E.B-100000),-1,E.B*0.40-10375,
B*0.45-15375
))))))))) personal_tax,E.C emp_id
from
(select (sum1+sum2+sum3+sum4+sum6-800) B,p1.emp_id C from
(select emp_id,sum(amount) sum1 from
payment_item where item_id in (0101,0102,0103,0104,0148)
and to_char(pay_date,'yyyy-mm')='2002-12' group by emp_id)p1,
(select emp_id,sum(amount)-decode(emp_id,4,500,
156,500,186,500,190,500,526,500,626,500,300) sum2
from payment_item where item_id in (0301,0308,0309)
and to_char(pay_date,'yyyy-mm')='2002-12' group by emp_id)p2,
(select emp_id,sum(amount) sum3 from payment_item
where item_id in (0110,0111,0206)
and to_char(pay_date,'yyyy-mm')='2002-12'
group by emp_id)p3,
(select emp_id,amount sum4 from payment_item
where item_id in (9999) and pay_id=2
and to_char(pay_date,'yyyy-mm')='2002-12')p4,
(select emp_id,sum(amount) sum6 from payment_item
where item_id in (0152,0153,0154)
and to_char(pay_date,'yyyy-mm')='2002-12'
group by emp_id)p6
where p1.emp_id=p2.emp_id
and p1.emp_id=p3.emp_id
and p1.emp_id=p4.emp_id
and p1.emp_id=p6.emp_id)E
SQL> desc test3;//是个视图,由多个表字段组成
名称 是否为空?类型
------------------------------- -------- ----
PERSONAL_TAX NUMBER
EMP_ID NOT NULL NUMBER(4)
SQL> desc payroll_item;//是张表
名称 是否为空?类型
------------------------------- -------- ----
PAY_ID NOT NULL NUMBER(2)
EMP_ID NOT NULL NUMBER(4)
ITEM_ID NOT NULL CHAR(4)
AMOUNT NUMBER(12,2)
update payroll_item
set amount=(
select personal_tax
from test3
where test3.emp_id=payroll_item.emp_id)
where exists (select * from test3 where test3.emp_id=payroll_item.emp_id)
and payroll_item.item_id='0157'
and payroll_item.pay_id=11;
SQL> desc test3;//是个视图,由多个表字段组成
名称 是否为空?类型
------------------------------- -------- ----
PERSONAL_TAX NUMBER
EMP_ID NOT NULL NUMBER(4)
SQL> desc payroll_item;//是张表
名称 是否为空?类型
------------------------------- -------- ----
PAY_ID NOT NULL NUMBER(2)
EMP_ID NOT NULL NUMBER(4)
ITEM_ID NOT NULL CHAR(4)
AMOUNT NUMBER(12,2)
SQL> update payroll_item
2 set amount=(
3 select personal_tax
4 from test3
5 where test3.emp_id=payroll_item.emp_id)
6 where test3.emp_id=payroll_item.emp_id
7 and payroll_item.item_id='0157'
8 and payroll_item.pay_id=11;
where test3.emp_id=payroll_item.emp_id
*
ERROR 位于第 6 行:
ORA-00904: invalid column name
为什么会报这个错误》??
原因何在???