原有的交叉视图如下,其中a.s_wage_id的值始终为1,视图查询的结果只有一行:
create or replace view v_wa_item as
select a.s_wage_id ,
SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as 基本工资,
SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as 岗位工资,
SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as 工龄工资,
SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as 绩效工资,
SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as 边远补助
from WA_DATA a group by a.s_wage_id --由pro_creatitemview动态创建
我现在期望对该视图作如下改写,其中a.s_wage_id的值始终依然为1;
把a.n_item_value的类型改为字符类型,里面不再是保存数字而是公式;
因此就不能用sum,那么该怎么改写这个视图才能让结果中依然只有一行记录,并且显示的是公式?
create or replace view v_wa_item as
select a.s_wage_id ,
SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as 基本工资,
SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as 岗位工资,
SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as 工龄工资,
SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as 绩效工资,
SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as 边远补助
from WA_DATA a group by a.s_wage_id --由pro_creatitemview动态创建
我现在期望对该视图作如下改写,其中a.s_wage_id的值始终依然为1;
把a.n_item_value的类型改为字符类型,里面不再是保存数字而是公式;
因此就不能用sum,那么该怎么改写这个视图才能让结果中依然只有一行记录,并且显示的是公式?
create or replace view v_wa_formula as
select 1 as s_wage_id ,
MAX(CASE WHEN a.s_item_code = 1001 then a.S_FML_CAL else null end) as 基本工资,
MAX(CASE WHEN a.s_item_code = 1002 then a.S_FML_CAL else null end) as 岗位工资,
MAX(CASE WHEN a.s_item_code = 1003 then a.S_FML_CAL else null end) as 工龄工资,
MAX(CASE WHEN a.s_item_code = 1004 then a.S_FML_CAL else null end) as 绩效工资,
MAX(CASE WHEN a.s_item_code = 1005 then a.S_FML_CAL else null end) as 边远补助
from WA_ITEM a --由pro_creatitemview动态创建