Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle query

Posted on 2012-04-10
4
Medium Priority
?
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37827716
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
ID: 37827777
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 37827811
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
ID: 37832646
thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

721 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