3,491
社区成员
发帖
与我相关
我的任务
分享
create or replace view sap_info_view as
select distinct v.employee_number,
v.name,
v.sex,
v.birthdate,
v.native_place,
v.post_name,
v.post_level,
v.skill_name,
v.skill_level,
v.nation,
v.education,
v.school,
v.empoly_type,
v.start_work_time,
v.entry_work_time,
v.entry_cctv_time,
v.political,
v.dept_code,
v.dept_name,
v.id --因创建hibernate级联而创建的假id 实际没有用
from ((select t.EMPLOYEE_NUMBER,
t.NAME,
t.sex,
to_char(to_date(t.BIRTHDATE, 'yyyy-MM-dd'), 'yyyyMMdd') as BIRTHDATE,
t.NATIVE_PLACE,
sdp.describe as post_name,
sdpl.describe as post_level,
sdsp.describe as skill_name,
sdsl.describe as skill_level,
sdn.describe as nation,
sde.describe as education,
t.school,
sdg.describe as empoly_type,
to_char(to_date(t.start_work_time, 'yyyy-MM-dd'), 'yyyyMMdd') as start_work_time,
to_char(to_date(t.entry_work_time, 'yyyy-MM-dd'), 'yyyyMMdd') as entry_work_time,
to_char(to_date(t.entry_cctv_time, 'yyyy-MM-dd'), 'yyyyMMdd') as entry_cctv_time,
sdpli.describe as political,
t.dept_code as dept_code,
t.dept_name as dept_name,
t.name as id --因创建hibernate级联而创建的假id 实际没有用
from pb_party_temp t
left outer join sap_dic_post sdp
on sdp.code = t.post_name
left outer join sap_dic_postlevel sdpl
on t.post_level = sdpl.code
left outer join sap_dic_skillpost sdsp
on t.skill_name = sdsp.id
left outer join sap_dic_skilllevel sdsl
on t.skill_level = sdsl.code
left outer join sap_dic_nation sdn
on t.nation = sdn.code
left outer join sap_dic_education sde
on t.education = sde.code
left outer join sap_dic_group sdg
on t.EMPOLY_TYPE = sdg.code
left outer join sap_dic_political sdpli
on sdpli.code = t.political
) union all
(select s.employee_number,
s.name,
s.sex,
s.birthdate,
s.native_place,
sp.post_name,
sp.post_level,
sdsp.describe as skill_name,
sdsl.describe as skill_level,
sdn.describe as nation,
sev.education as education,
sev.school,
sdg.describe as empoly_type,
st.date_value as start_work_time,
stt.date_value as entry_work_time,
sttt.date_value as entry_cctv_time,
spv.political as political,
sdept.dept_code as dept_code,
sdept.dept_name as dept_name,
s.name as id --因创建hibernate级联而创建的假id 实际没有用
from sap_baseinfo s
left outer join sap_post_view sp
on s.employee_number = sp.employee_number
--left outer join sap_post sp on sp.employee_number =
-- s.employee_number
--left outer join sap_dic_post sdp on sp.post = sdp.code
--left outer join sap_dic_postlevel sdpl on sp.post_level =
-- sdpl.code
left outer join sap_professional_title spt on spt.employee_number =
s.employee_number
left outer join sap_dic_skillpost sdsp on spt.skill_type =
sdsp.id
left outer join sap_dic_skilllevel sdsl on spt.skill_level =
sdsl.code
left outer join sap_additional sa on s.employee_number =
sa.employee_number
left outer join sap_dic_nation sdn on sa.nation = sdn.code
left outer join sap_education_view sev on sev.employee_number=s.employee_number
--left outer join sap_education se on se.employee_number =
-- s.employee_number
--left outer join sap_dic_education sde on se.education = sde.code
left outer join sap_group sg on sg.employee_number =
s.employee_number
left outer join sap_dic_group sdg on sg.group_code = sdg.code
left outer join sap_time st on st.employee_number =
s.employee_number
and st.date_type = 'Z1'
left outer join sap_time stt on stt.employee_number =
s.employee_number
and stt.date_type = 'Z9'
left outer join sap_time sttt on sttt.employee_number =
s.employee_number
and sttt.date_type = 'Z2'
left outer join sap_political_view spv on spv.employee_number=s.employee_number
--left outer join sap_political spl on spl.employee_number =
-- s.employee_number
-- left outer join sap_dic_political sdpli on sdpli.code =
-- spl.political_status
left outer join sap_dept_view sdept on s.employee_number=sdept.employee_number
--left outer join sap_employee_dept sed on sed.employee_number =
-- s.employee_number
--left outer join sap_department sdept on sed.dept_code =
-- sdept.dept_code
))v;
create or replace view sap_dept_view as
select t.employee_number, wm_concat(sd.l_dept_name) as dept_name,wm_concat(sd.dept_code) as dept_code
from sap_employee_dept t
left join sap_department sd
on t.dept_code = sd.dept_code
group by t.employee_number;
create or replace view sap_education_view as
select t.employee_number, wm_concat(sp.describe) as education,wm_concat(t.school) as school
from sap_education t
left join sap_dic_education sp
on t.education = sp.code
group by t.employee_number;
create or replace view sap_political_view as
select t.employee_number, wm_concat(sp.describe) as political
from sap_political t
left join sap_dic_political sp
on t.political_status = sp.id
group by t.employee_number;
create or replace view sap_post_view as
select t.employee_number, wm_concat(sp.describe) as post_name,wm_concat(sdp.describe) as post_level
from sap_post t
left join sap_dic_post sp
on t.post = sp.code
left join sap_dic_postlevel sdp
on t.post_level = sdp.code
group by t.employee_number;