oracle query

Hi I have a requirement as follows

Casual Employees
==============
Deptno
empno
ename
location
employee type
service end date

permanent_employee
=================
Deptno
empno
ename
location
employee type
service end date

course_table
==========
course id
course name
empno

i want the data grouped on empno(this empno is  coming from different tables but is unique)
data should be on group by empno and the location should be current location, for eg.
based on service date(max service date or service date is null).
so the below query shoud show latest location for all 3 rows. ie., center2.

Location             serviceenddate
center 2                  null
center 1               26-APR-08
center 1                26-APR-08


select
Deptno,
empno,
ename,
location,
employee type,
service end date,
course id,
course name
from Casual_Employees,course_table
where Casual_Employees.empno=course_table.empno

union
select
Deptno,
empno,
ename,
location,
employee type,
service end date,
course id,
course name
from permanent_employee,course_table
where permanent_employee.empno=course_table.empno

thanks.
Sarma VadlamaniprogrammeranalystAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Given my setup above try this:
select
emp_no, emp_stat, last_name, first_name,
max(case when rn=1 then location end) over(partition by sin_no) my_loc,
sevice_enddate, course, sin_no
from (
    select emp_no, emp_stat, last_name, first_name, location, sevice_enddate, course, sin_no,
    	row_number() over(partition by sin_no order by sin_no,sevice_enddate desc nulls first) rn
 from
	(select * from tab1)
)
order by emp_no
/ 

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Without actual sample data it is difficult to come up with a working example for your expected results.

But I think you want to use ROW_NUMBER to come up with the 'max' and correct row to return.

This should be the basics and give you the idea but I doubt it is copy/paste ready:

select location, service_date from (
   select location, service_date, row_number() over(partition by empno order by empno,location,service_date desc) rn
from
(select * from permanent_employee union all select * from casual_employee)
)
where rn=1
/
0
 
Sarma VadlamaniprogrammeranalystAuthor Commented:
SIN_NO            EMP_NO       EMP_STAT      LAST_NAME         FIRST_NAME        Location          Sevice_enddate    course
111                       741               inactive             MCHREON          GARY                     location one      11-Apr-04                oracle
111                      742                      inactive             MCHERON           GARY                     location two        20-Jul-02                 vb
111                      743                      on leave            MCHREON            GARY                    currentlocation       null                        java
                                    
ok above is the sample data coming from 3 tables as mentioned above and common
field is sinno. location one and location two should replaced with currentlocation as that is the latest location.

i tried the above query but it is not working.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
For sample data, I need expected results.

What I need to do is create a test case, load up your sample data then I can write a query that gives me your expected results.

>> i tried the above query but it is not working.

Define "not working".

Here is the setup I created based on the data you posted above:
/*
drop table tab1 purge;
create table tab1( SIN_NO number,EMP_NO number,EMP_STAT varchar2(15),LAST_NAME varchar2(15),FIRST_NAME varchar2(15),Location varchar2(15),Sevice_enddate date,course varchar2(15) );

insert into tab1 values(111,741,'inactive','MCHREON','GARY','location one',to_date('11-Apr-04','DD-Mon-YY'),'oracle');
insert into tab1 values(111,742,'inactive','MCHERON','GARY','location two',to_date('20-Jul-02' ,'DD-Mon-YY'),'vb');
insert into tab1 values(111,743,'on leave','MCHREON','GARY','currentlocation',null,'java');
commit;
*/

Open in new window


Using the query I posted above (changing the column names to match yours), I get the following results:
location one    04/11/2004 00:00:00
location two    07/20/2002 00:00:00
currentlocation

Open in new window


I assume this isn't correct.  Please add additional data and provide the expected results from the data you provided.
0
 
Sarma VadlamaniprogrammeranalystAuthor Commented:
expected result is group by sin no.

i think you are using only one table but i am getting data from 3 tables, and common field is sinno.

need
empno,emp_stat,last_name,first_name, location, service_enddate,course
from table1
table2
table3
and all 3 records location should be replaced with currentlocation.

thanks.
0
 
slightwv (䄆 Netminder) Commented:
I can only create a test case based on what you provide me.  Please add data to the setup I posted then provide me the expected results.



I don't think I need all three tables.  If you understand what the query I posted is doing, you should be able to integrate it into what you have.  Besides two of the tables is more or less one with a union all between them...  I would do that in a single inline view then join it to the third.

>>expected result is group by sin no.

The "group by" is the "partition by" inside the row_number function.
0
 
Sarma VadlamaniprogrammeranalystAuthor Commented:
I aplogize if i didn't give exact scenario.
expected result is as below.


empno  emp_stat   lastname      firstname     location         Sevice_enddate    course   sinno
741    inactive     MCHREON      GARY       currentlocation     11-Apr-04             oracle     111
742    inactive     MCHREON      GARY       currentlocation     20-Jul-02                 vb          111
743    inactive     MCHREON      GARY       currentlocation      null                        java          111

thanks.
0
 
slightwv (䄆 Netminder) Commented:
>>expected result is as below.

How do you get 'inactive'  and 'currentlocation' for all three rows? I understand currentlocation is because there is no sevice_enddate.  I don't see how you get 'inactive'.
0
 
Sarma VadlamaniprogrammeranalystAuthor Commented:
sorry typing mistake 3rd row is on leave.
0
 
Sarma VadlamaniprogrammeranalystAuthor Commented:
yes only the location should be changed to current location once the service date is maximum or null.
i tried with lag function but not giving correct result.
0
 
slightwv (䄆 Netminder) Commented:
Thanks.  Give me a little time.  I think I understand now.
0
 
Sarma VadlamaniprogrammeranalystAuthor Commented:
perfect it is working thanks very much.
0
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.

All Courses

From novice to tech pro — start learning today.