anumoses
asked on
oracle query
I need help in a decode or case statement
Need to get department_name
if Site_code from dept_staff table is null and reason_code from dept_staff = 'SH' then
site_name = department_name
else
site_name = site_name from sites table.
Need to get department_name
if Site_code from dept_staff table is null and reason_code from dept_staff = 'SH' then
site_name = department_name
else
site_name = site_name from sites table.
ASKER
create or replace view off_duty (site_code,
site_code1,
department_id, department_name,
schedule_date, start_time, start_ampm,
end_time, end_ampm, total_hours,
short_description, reason_code, payroll_id, emp_name, mail_last_name)
as
select d.site_code,ds.site_code,e u.departme nt_id,d.de partment_n ame,
ds.schedule_date,ds.start_ time,ds.st art_ampm,
ds.end_time,ds.end_ampm,ds .total_hou rs,
nar.short_description,nar. reason_cod e,
ds.payroll_id,FIRST_NAME|| ','||MAIL_ LAST_NAME, mail_last_ name
from emp_unexcld eu,
department d,
dept_staff ds,
not_avail_reason nar
where eu.payroll_id = ds.payroll_id
and ds.department_id = d.department_id(+)
and ds.reason_code = nar.reason_code
and nar.schedule_required_flag = 'Y'
-------------------------- ---------- ---
select a.emp_name,
to_char(schedule_date,'fmD ay')WEED_D AY,
schedule_date,
replace(to_char(a.start_ti me/100, 'fm00.00'), '.', ':')||a.start_ampm start_time,
replace(to_char(a.end_time /100, 'fm00.00'), '.', ':')||a.end_ampm end_time,
to_char(a.total_hours/100, 'fm00.00') total_hours,
a.short_description,
s.site_name
from off_duty a, blood_drives bd,sites s,dept_sched des
where a.site_code = s.site_code(+)
and a.schedule_date = bd.drive_date(+)
and a.site_code = bd.site_code(+)
and des.department_id = a.department_id
and a.schedule_date between trunc(sysdate,'d') and last_sched_date
and a.department_id = 12
order by a.department_id,a.mail_las t_name,a.p ayroll_id, schedule_d ate
-------------------------
The reason to do this because of the outer join problem that I had opened a question earlier.
Now I want the department_name with a decode or case statement
site_code1,
department_id, department_name,
schedule_date, start_time, start_ampm,
end_time, end_ampm, total_hours,
short_description, reason_code, payroll_id, emp_name, mail_last_name)
as
select d.site_code,ds.site_code,e
ds.schedule_date,ds.start_
ds.end_time,ds.end_ampm,ds
nar.short_description,nar.
ds.payroll_id,FIRST_NAME||
from emp_unexcld eu,
department d,
dept_staff ds,
not_avail_reason nar
where eu.payroll_id = ds.payroll_id
and ds.department_id = d.department_id(+)
and ds.reason_code = nar.reason_code
and nar.schedule_required_flag
--------------------------
select a.emp_name,
to_char(schedule_date,'fmD
schedule_date,
replace(to_char(a.start_ti
replace(to_char(a.end_time
to_char(a.total_hours/100,
a.short_description,
s.site_name
from off_duty a, blood_drives bd,sites s,dept_sched des
where a.site_code = s.site_code(+)
and a.schedule_date = bd.drive_date(+)
and a.site_code = bd.site_code(+)
and des.department_id = a.department_id
and a.schedule_date between trunc(sysdate,'d') and last_sched_date
and a.department_id = 12
order by a.department_id,a.mail_las
-------------------------
The reason to do this because of the outer join problem that I had opened a question earlier.
Now I want the department_name with a decode or case statement
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
case when dept_staff.site_code is null and dept_staff.reason_code = 'SH' then department_name else (select site_name from sites_table where.....) end
Not sure about your where clause in the sub-select. You might be able to join the tables but will need more information.