17,086
社区成员
发帖
与我相关
我的任务
分享
select
cv_js_code,cv_name,cv_sex,nvl(cv_work_years,0),cv_degree,js_modify_dt,
CV_NAMESECRET,cv_photo,round((sysdate -CV_BIRTHDAY)/365),
CVX_POS1_DESC||','||CVX_POS2_DESC||','||CVX_POS3_DESC,
CV_LOC_NOW,CVX_EXP_WORK_LOC1, CVX_EXP_WORK_LOC2, CVX_EXP_WORK_LOC3,cvx_fun1,
CVX_POS1 ,CV_SELF_SCORE,CVX_EXP_SALARY ,
f_get_work(cv.cv_code) --把上面的子查询修改.
from jobseeker,cv,cv_expect_work
where CV_VALIDITY='2' and CV_SECURITY=0 and js_code=cv_js_code and cv_code=cvx_cv_code
order by js_modify_dt desc
create or replace function F_GET_WORK(
cv_code IN number
)
return varchar2
as
works varchar2(1000);
begin
select workinfo into works from v_cv_onework where cvw_cv_code=cv_code;
return works;
if works='' or works is null then
return '-';
end if ;
exception
when others then
return '-';
end F_GET_WORK;
select
cv.cv_js_code,decode(cv.CV_NAMESECRET,1,substr(cv.cv_name,1,1),cv.cv_name),
cv.cv_sex,nvl(cv.cv_work_years,0),cv.cv_degree,js.js_modify_dt,
cv.CV_NAMESECRET,cv.cv_photo,round((sysdate -cv.CV_BIRTHDAY)/365),
cew.CVX_POS1_DESC||','||cew.CVX_POS2_DESC||','||cew.CVX_POS3_DESC,
cv.CV_LOC_NOW,cew.CVX_EXP_WORK_LOC1, cew.CVX_EXP_WORK_LOC2, cew.CVX_EXP_WORK_LOC3,cew.cvx_fun1,
cew.CVX_POS1 ,cv.CV_SELF_SCORE,cew.CVX_EXP_SALARY ,d.workinfo
from jobseeker js,cv,cv_expect_work cew,
(select cvw_cv_code,
max(to_char(CVW_FROM_DT,'YYYY-MM-DD')||'至'||decode(CVW_END_DT,null,'现在',to_char(CVW_END_DT,'YYYY-MM-DD'))||','||CVW_COMPANY||':'||CVW_POSITION_DESC||','||CVW_DESC) as workinfo
from cv_work group by cvw_cv_code) d
where CV_VALIDITY='2' and CV_SECURITY=0 and js_code=cv_js_code and cv_code=cvx_cv_code and d.cvw_cv_code=cv.cv_code
order by js.js_modify_dt desc