current allocated/future/released '&' IN PL-SQL
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
select *
from (select sub_user_pro.emp_id,
sub_user_pro.emp_name,
er.role_name,
ep.project_name,
ep.project_won,
eup.project_id, -- NO NEED TO SHOWN
to_char(eup.join_date, 'MM/DD/YYYY') as join_date,
to_char(eup.expected_release_date, 'MM/DD/YYYY') as expected_release_date,
to_char(ep.project_start_date, 'MM/DD/YYYY') as project_start_date,
to_char(ep.project_end_date, 'MM/DD/YYYY') as project_end_date,
sub_user_pro.status,
eu_pm.first_name || ' ' || eu_pm.last_name as pm_name,
eu_am.first_name || ' ' || eu_am.last_name as am_name
from eis_user_projects eup,
eis_projects ep,
eis_roles er,
eis_users eu_pm,
eis_users eu_am,
(select eu.id as emp_id,
eu.first_name || ' ' || eu.last_name emp_name,
decode(sub_current.status,
null,
decode(sub_future.status,
null,
sub_past.status,
sub_future.status),
sub_current.status) as status,
decode(sub_current.eup_Id,
null,
decode(sub_future.eup_Id,
null,
sub_past.eup_Id,
sub_future.eup_Id),
sub_current.eup_Id) as eup_Id
from eis_users eu,
(select eup.id as eup_Id,
eup.emp_id,
(case
when (EXTRACT(year FROM expected_release_date) =
EXTRACT(year FROM sysdate)) and
(EXTRACT(month FROM expected_release_date) =
EXTRACT(month FROM sysdate)) then
----------------------------------------------------------------------------------------------------------------------
case
when (EXTRACT(DAY FROM expected_release_date) =
to_number( TO_CHAR( LAST_DAY(sysdate) ,'dd')) ) then
'Month-end Released'
else
'Released before Month end'
end
--
else 'Allocated'
end) as status
from eis_user_projects eup
where to_date(to_char(sysdate, 'dd/mm/yyyy'),
'dd/mm/yyyy') BETWEEN eup.join_date AND
EUP.EXPECTED_RELEASE_DATE) sub_current,
(select eup.id as eup_Id,
eup.emp_id,
'Future Allocated' STATUS
from eis_user_projects eup,
(select eup.emp_id, min(eup.join_date) join_date
from eis_user_projects eup
where to_date(to_char(sysdate, 'dd/mm/yyyy'),
'dd/mm/yyyy') < eup.join_date
group by eup.emp_id) latest_future_pro
where eup.emp_id = latest_future_pro.emp_id
and eup.join_date = latest_future_pro.join_date) sub_future,
(select eup.id as eup_Id,
eup.emp_id,
'UnAllocated' STATUS
from eis_user_projects eup,
(select eup.emp_id, max(eup.join_date) join_date
from eis_user_projects eup
where to_date(to_char(sysdate, 'dd/mm/yyyy'),
'dd/mm/yyyy') >
eup.expected_release_date
group by eup.emp_id) latest_past_pro
where eup.emp_id = latest_past_pro.emp_id
and eup.join_date = latest_past_pro.join_date) sub_past
where eu.id = sub_past.emp_id(+)
and eu.id = sub_current.emp_id(+)
and eu.id = sub_future.emp_id(+)
--and eu.Id <>801660
and eu.active_status = 'Y') sub_user_pro
where sub_user_pro.eup_id = eup.id
and eup.project_id = ep.id
and eup.user_role_id = er.id
and sub_user_pro.emp_id = eup.emp_id
and eu_pm.id = ep.pm_emp_id
and eu_am.id = ep.am_emp_id
---------- AM PM SM
and (('&in_role_abbrv' = 'AM' and ep.am_emp_id = '&in_emp_id') or
('&in_role_abbrv' = 'PM' and ep.pm_emp_id = '&in_emp_id')
or ('&in_role_abbrv' = 'SM'))
-----------
--and ep.pm_emp_id = IN_EMP_iD
--and ep.am_emp_id = 801660
) sub_no_condition
where (('&in_searchEmp_id' is null) or
('&in_searchEmp_id' is not null and
sub_no_condition.emp_id = '&in_searchEmp_id'))
and (('&in_status' is null) or ('&in_status' is not null and
sub_no_condition.status = '&in_status'))
--ORDER BY emp_id
/*) SUB_RESULT
WHERE ROWNUM <= v_last_record)
WHERE RANK >= v_first_record;*/
posted on 2009-05-19 23:51 dolphin_bobo 阅读(105) 评论(0) 编辑 收藏 举报