| Query on Outer Join [message #193363] |
Fri, 15 September 2006 18:16  |
vivek2all
Messages: 3
Registered: September 2006
|
Junior Member |
|
|
Hi Guys,
Anybody pls help me out in solving this issue. I've have a query with an outer join on a subquery.
Select j.emplid,j.empl_rcd,j.effdt,s.effdt from job j, savings_plan s
WHERE j.empl_rcd = 0
AND j.effdt =
(SELECT MAX (j1.effdt)
FROM job j1
WHERE j1.emplid = j.emplid
AND j1.empl_rcd = j.empl_rcd
AND j1.effdt <= '15-JUN-2006')
AND j.effseq =
(SELECT MAX (j2.effseq)
FROM job j2
WHERE j2.emplid = j.emplid
AND j2.empl_rcd = j.empl_rcd
AND j2.effdt = j.effdt)
AND s.emplid(+) = j.emplid
AND s.empl_rcd(+) = j.empl_rcd
AND s.plan_type(+) = '40'
AND s.benefit_nbr(+) = 0
AND ( s.effdt =
(SELECT MAX (s1.effdt)
FROM savings_plan s1
WHERE s1.emplid = s.emplid
AND s1.empl_rcd = s.empl_rcd
AND s1.plan_type = s.plan_type
AND s1.benefit_nbr = s.benefit_nbr
AND s1.effdt <= '15-JUN-2006')
OR s.effdt IS NULL
)
And j.emplid = '055422'
This is a part of my query. The first table (job) has a record and the second table(savings_plan) in this list has a row dated (01-Sep-2006) and my condition filters it out b'cos it is
<= 15-Jun-2006 (i.e row available in the second table is a future dated one). This query should retrieve a row based out of the driving table (job) but its not the case. Even though i've applied an outer join it is only null.
Since because we cannot apply outer join on a subquery, i had placed an IS NULL condition on that field alone.
Is this query correct or is there anythng wrong in it????????
|
|
|
|
|
|
| Re: Query on Outer Join [message #193379 is a reply to message #193373] |
Fri, 15 September 2006 23:27  |
vivek2all
Messages: 3
Registered: September 2006
|
Junior Member |
|
|
If i hard code the date directly and use the join operator, then i'm getting the result correctly.
With this query in place, for some employees who have not got a row in second table(savings_plan), the row is getting retrieved properly. For this particular row alone, which has got a future dated entry in the second table, no row is getting retrieved.
I don't understand what is happening????
|
|
|
|
| Re: Query on Outer Join [message #193449 is a reply to message #193363] |
Sat, 16 September 2006 19:31 |
shoblock
Messages: 325
Registered: April 2004
|
Senior Member |
|
|
the reason it doesn't work is because "s.effdt" in the main query is JOINED, not OUTER-JOINED, to the subquery. of course, oracle would give an error that you aren't allowed to outer-join to a subquery, so you can't do that. but just as you outer-joined S to the constants, it needs to be outer-joined to the second occurrance, S1.
solution - in line views. join J and S1 in an in-line view to get the J info plus the max effdt from S1. I made this an outer-join, because maybe some job doesn't have a savings plan, which I think is your intention. then outer-join S to the in-line view:
select js.emplid, js.empl_rcd, js.effdt, s.effdt
from savings_plan s,
(select j.emplid, j.empl_rcd, j.effdt, MAX (s1.effdt) s_effdt
from job j, savings_plan s1
WHERE j.empl_rcd = 0
And j.emplid = '055422'
AND j.effdt = (
SELECT MAX (j1.effdt)
FROM job j1
WHERE j1.emplid = j.emplid
AND j1.empl_rcd = j.empl_rcd
AND j1.effdt <= '15-JUN-2006'
)
AND j.effseq = (
SELECT MAX (j2.effseq)
FROM job j2
WHERE j2.emplid = j.emplid
AND j2.empl_rcd = j.empl_rcd
AND j2.effdt = j.effdt
)
and s1.emplid(+) = j.emplid
AND s1.empl_rcd(+) = j.empl_rcd
AND s1.plan_type(+) = '40'
AND s1.benefit_nbr(+) = 0
AND s1.effdt(+) <= '15-JUN-2006'
group by j.emplid, j.empl_rcd, j.effdt
) js
where s.emplid(+) = js.emplid
AND s.empl_rcd(+) = js.empl_rcd
AND s.plan_type(+) = '40'
AND s.benefit_nbr(+) = 0
AND s.effdt(+) = js.s_effdt
completely untested, so I may have missed something, but the concept will work.
|
|
|
|
| Re: Query on Outer Join [message #193452 is a reply to message #193449] |
Sat, 16 September 2006 23:16 |
vivek2all
Messages: 3
Registered: September 2006
|
Junior Member |
|
|
Yes, you are right. I've not outer-joined explicitly but i've given "s.effdt IS NULL" which caters to that. If that subquery doesn't return any row then it should be null. In that case, the "IS NULL" condition should work, i think.
But i don't understand why this works for some rows and doesn't apply for others.
Anyways, thanks for the response...
|
|
|
|
| Re: Query on Outer Join [message #193478 is a reply to message #193363] |
Sun, 17 September 2006 14:32 |
shoblock
Messages: 325
Registered: April 2004
|
Senior Member |
|
|
the line near the end of your original query
"AND ( s.effdt ="
No outer join symbol. that's the problem. if you leave out the "(+)" one any one join criteria, then all the ones you included are meaningless. you correctly wrote "AND s.plan_type(+) = '40'", but if you had written "AND s.plan_type = '40'" (no OJ), then that would also break the rules of outer-joins. if the table S is to be correctly outer-joined, then ALL references to it in the WHERE clause (whether to other tables or to constants or functions), MUST BE OUTER-JOINED.
adding "s.effdt IS NULL" DOES NOT cater to anything. If it did, then your query would work, but it doesn't.
the reason your query works for some rows is because it works ONLY for the rows that don't need to be outer-joined. remove ALL outer-joines from your query, and it will work for SOME rows. to get it to work for ALL rows, you must follow the rules of outer-joins.
Now, did you try running my query? does it work?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
