Home » SQL & PL/SQL » SQL & PL/SQL » Query on Outer Join
Query on Outer Join [message #193363] Fri, 15 September 2006 18:16 Go to next message
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 #193373 is a reply to message #193363] Fri, 15 September 2006 22:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What happens if you add
AND s.effdt (+) <= '15-JUN-2006'
at the very end.

Ross Leishman
Re: Query on Outer Join [message #193379 is a reply to message #193373] Fri, 15 September 2006 23:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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?
Previous Topic: Where is my table stored?
Next Topic: Finding atleast two records
Goto Forum:
  


Current Time: Wed Dec 07 13:02:49 CST 2016

Total time taken to generate the page: 0.12875 seconds