Sarma Vadlamani
asked on
oracle query
Dept table
deptno dname
10 abc
20 bbb
30 ccc
40 ddd
term table
termid termstartdate status deptno
1 2000/01/01 active 10
2 2001/01/01 active 20
3 2002/01/01 active 30
4 2003/01/01 complete 40
emp table
empno deptno emptype
101 10 manager
102 20 clerk
casework table
casework
caseid deptno casedetials
1001 10 details1
1002 30 details2
query to return result as below
deptno dname termid termstartdate status deptno empno deptno emptype ceid dno asedls
10 abc 1 2000/01/01 active 10 101 10 manager 1001 10 det1
20 bbb 2 2001/01/01 activ 20 102 20 clerk null null null
30 ccc 3 2002/01/01 active 30 null null null 1002 30 det2
40 ddd 4 2003/01/01 complete 40 null null null null null null
how can i achieve this result i use this query in oracle reqports.
thanks
deptno dname
10 abc
20 bbb
30 ccc
40 ddd
term table
termid termstartdate status deptno
1 2000/01/01 active 10
2 2001/01/01 active 20
3 2002/01/01 active 30
4 2003/01/01 complete 40
emp table
empno deptno emptype
101 10 manager
102 20 clerk
casework table
casework
caseid deptno casedetials
1001 10 details1
1002 30 details2
query to return result as below
deptno dname termid termstartdate status deptno empno deptno emptype ceid dno asedls
10 abc 1 2000/01/01 active 10 101 10 manager 1001 10 det1
20 bbb 2 2001/01/01 activ 20 102 20 clerk null null null
30 ccc 3 2002/01/01 active 30 null null null 1002 30 det2
40 ddd 4 2003/01/01 complete 40 null null null null null null
how can i achieve this result i use this query in oracle reqports.
thanks
David VanZandt
Speaking for myself, I'm here to help others learn and work through problems -- but not to do their work, or classwork, for them. Unless of course there are contracting fees on the table. If you have sample code to attach, showing your work so far, I would be glad to do my best for you.
Looks like a fairly simple query. What aren't you telling us? What do you have so far? What isn't working about it?
I hate to say it, but this question is structured like many questions that go on for 20 or more posts before we get to the real question.
The first set of issues I see are what is the driving table, what are you trying to solve?
It should be a simple join between DEPT and TERM and then outer joins to the other 2 tables, but I'm afraid what you need is more complicated than that and you aren't explaining it correctly.
I hate to say it, but this question is structured like many questions that go on for 20 or more posts before we get to the real question.
The first set of issues I see are what is the driving table, what are you trying to solve?
It should be a simple join between DEPT and TERM and then outer joins to the other 2 tables, but I'm afraid what you need is more complicated than that and you aren't explaining it correctly.
ASKER
the driving table is dept and all records from term should come if there is other table data then it should come or just show null values
i tried with union but the result is not what i am expecting.
how can I use more than one outer join in oracle as it will not support.
thanks.
i tried with union but the result is not what i am expecting.
how can I use more than one outer join in oracle as it will not support.
thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the response but this is not solving my proble,
i think the above query only does the left outer join but my requirement is
driving column is deptno
and one deptno can have multiple terms and each term can have multiple employees and multiple cases.
what i want is for one dept number the active status term along with all employee types and the only the latest case details
for eg. dept 10 can have 3 terms with each starting on different dates - for this I am using term start date
and each term may have 3 or 4 employee types information starting on different dates for this iam using employee type change date. so all employee types should come
and each term may have 3 or 4 case reviews but require only the latest casereveiws. for this I am using case review date.
thanks.
i think the above query only does the left outer join but my requirement is
driving column is deptno
and one deptno can have multiple terms and each term can have multiple employees and multiple cases.
what i want is for one dept number the active status term along with all employee types and the only the latest case details
for eg. dept 10 can have 3 terms with each starting on different dates - for this I am using term start date
and each term may have 3 or 4 employee types information starting on different dates for this iam using employee type change date. so all employee types should come
and each term may have 3 or 4 case reviews but require only the latest casereveiws. for this I am using case review date.
thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks