Solved

oracle query

Posted on 2012-04-10
4
334 Views
Last Modified: 2012-04-11
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.
0
Comment
Question by:anumoses
  • 2
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now