We help IT Professionals succeed at work.

decode or case statement in oracle

anumoses
anumoses used Ask the Experts™
on
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,
                                                 ss_mob)
             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,d.ss_mob
  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,'fmDy')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,
         a.department_id,
         a.department_name,
         a.ss_mob,
         case when a.site_code1 is not null then a.site_code1 else a.site_code end site_code
  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 = 5
   and a.payroll_id = 'ZZW0024100'
   order by a.department_id,a.mail_last_name,a.payroll_id,schedule_date
-----------------------------------------
select a.emp_name,
       to_char(schedule_date,'fmDy')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,
         a.department_id,
         a.department_name,
         a.ss_mob,
         case when a.site_code1 is not null then a.site_code1 else a.site_code end site_code,
         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 = 10
   and a.payroll_id = 'ZZW0024538'
   order by a.department_id,a.mail_last_name,a.payroll_id,schedule_date
--------------------------------------
Department_id 10  (S)
department_id 5    (M)
---------------------
If a.ss_mob = 'S' then I need to take department_id
If a.ss_mob = 'M' then I need to have
case when a.site_code1 is not null then a.site_code1 else a.site_code end
How can I combine both in one case statement?
emp-file.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You can nest case statements:

case
   when a.ss_mob = 'S' then department_id
   when a.ss_mob = 'M' then
       case when a.site_code1 is not null then a.site_code1 else a.site_code end  
end

Author

Commented:
If I want to get this info in one column and name it as site_code,
department_id is number and site_code is varchar. So how can I make sure that I dont get a alpha numeric error?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Use to_char around the number?

Author

Commented:
thanks