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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.