Solved

oracle query

Posted on 2012-04-10
4
339 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)
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 9 45
Read only access to a Procedure in oracle? 4 65
oracle- set role and grant privileges 6 28
make null the repeated levels 2 20
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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