Oracle select 判断

创建项目绩效自评视图

最新推荐文章于 2024-10-23 17:54:59 发布

原创最新推荐文章于 2024-10-23 17:54:59 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个SQL视图的创建过程，该视图整合了项目评价、资金及年度绩效数据，用于评估项目的财务执行状况和绩效目标完成情况。通过连接多个表，实现了对项目审核状态、预算执行、得分等关键指标的综合展示。

create or replace view V_PD_SELF_EVALUATION as
select PD_PROJECT_EVALUATE.ROW_ID,PD_PROJECT_EVALUATE.FIRST_PROJ,
PD_PROJECT_EVALUATE.PROJECT_CODE,PD_PROJECT_EVALUATE.PROJECT_NAME,PD_PROJECT_EVALUATE.USE_OFFICE,
case PD_PROJECT_EVALUATE.AUDIT_STATUS
when '0' then '二级项目绩效自评下发经费处'
when '1' then '二级项目绩效自评下发经费处领导'
when '2' then '二级项目绩效自评司局'
when '3' then '二级项目绩效自评司局处领导'
when '4' then '二级项目绩效自评经费处'
when '5' then '二级项目绩效自评经费处领导'
when '6' then '二级项目绩效自评综合处'
when '7' then '二级项目绩效自评综合处领导'
else '二级项目绩效自评财政库'
end AUDIT_STATUS,
PD_PROJ_FUND.YEAR_INITIAL_BUDGET,PD_PROJ_FUND.YEAR_EXECUTE,PD_PROJ_FUND.EXECUTE_RATE_ALL,PD_PROJ_FUND.SCORE,
PD_ANNUAL_PERFORMANCE_MAIN.ANNUAL_GOAL,PD_ANNUAL_PERFORMANCE_MAIN.GOAL_COMPLETION
from PD_PROJECT_EVALUATE PD_PROJECT_EVALUATE
left join PD_PROJ_FUND PD_PROJ_FUND on PD_PROJ_FUND.FROM_ID=PD_PROJECT_EVALUATE.ROW_ID
left join PD_ANNUAL_PERFORMANCE_MAIN PD_ANNUAL_PERFORMANCE_MAIN on PD_ANNUAL_PERFORMANCE_MAIN.FROM_ID=PD_PROJECT_EVALUATE.ROW_ID;
comment on column V_PD_SELF_EVALUATION.ROW_ID is '主键id';
comment on column V_PD_SELF_EVALUATION.FIRST_PROJ is '一级项目';
comment on column V_PD_SELF_EVALUATION.PROJECT_CODE is '财政项目代码';
comment on column V_PD_SELF_EVALUATION.PROJECT_NAME is '项目名称';
comment on column V_PD_SELF_EVALUATION.USE_OFFICE is '司局代码';
comment on column V_PD_SELF_EVALUATION.AUDIT_STATUS is '审核状态';
comment on column V_PD_SELF_EVALUATION.YEAR_INITIAL_BUDGET is '全年预算数';
comment on column V_PD_SELF_EVALUATION.YEAR_EXECUTE is '全年执行数';
comment on column V_PD_SELF_EVALUATION.EXECUTE_RATE_ALL is '执行率';
comment on column V_PD_SELF_EVALUATION.SCORE is '总得分';
comment on column V_PD_SELF_EVALUATION.ANNUAL_GOAL is '预期目标';
comment on column V_PD_SELF_EVALUATION.GOAL_COMPLETION is '实际完成情况';