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.
LVL 6
anumosesAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Looks like both columns are coming from the off_duty view.

If so, it is a simple case using the logic you provided.  Since the view has a site_code and site_code1, I'm not sure which one to use but should be sometihng like:

case when a.site_code is null and a.reason_code = 'SH' then a.department_name else s.site_name end


I will not be able to get it 100% correct since I do not understand your tables and structure but the logic is straight forward.

Just think about what you typed in the original question and how a CASE statement works.
0
 
slightwv (䄆 Netminder) Commented:
Something like:

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.
0
 
anumosesAuthor Commented:
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,eu.department_id,d.department_name,
       ds.schedule_date,ds.start_time,ds.start_ampm,
       ds.end_time,ds.end_ampm,ds.total_hours,
       nar.short_description,nar.reason_code,
         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,'fmDay')WEED_DAY,
       schedule_date,
         replace(to_char(a.start_time/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_last_name,a.payroll_id,schedule_date
-------------------------
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
0
 
anumosesAuthor Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.