April 10, 2008 at 10:43 am
Heard a lot about this forums through co-worker. Hence I enrolled in here & I need some help !
Outer join is not yeilding me the results that I need. Query 1 fetches me 112 rows, Query 2 fetches me 91 rows & when I join both, I expect atleast 112 but I only get 91.
Almost forgot to mention, they are all distinct rows & there is no chance of dups in here.
Query1:
select hb.beenumber, ha.initialadjustmentamount
from proadjustment ha
join cmmadjustmenttype cmm on ha.cmmadjustmenttypeguid = cmm.cmmadjustmenttypeguid
join beebusiness hb on ha.beebusinessguid = hb.beebusinessguid and ha.probatchguid = hb.probatchguid
where cmm.adjustmenttypecode = 'RECRUIT'
Query2:
select sa_ss, totalprom
from [hqvm03\hqsqlinst05].sdk.dbo.DecCheckRegister
where totalprom > 0
Outer Join Query:
select isnull(ea.sa_ss,0) as SdkConsultantId,
coalesce(ea.totalprom,0) as SDKPromobonus,
coalesce(B.beenumber,0) as ProwessConsultantId,
coalesce(B.Amount,0) as ProwessPromobonus,
Diff = (coalesce(ea.totalprom, 0) - coalesce(b.Amount, 0))
from [hqvm03\hqsqlinst05].sdk.dbo.DecCheckRegister Ea
full outer join
(select A.Beenumber, A.Amount from
(select distinct hb.beenumber, sum(ha.initialadjustmentamount) as Amount
from proadjustment ha
join cmmadjustmenttype cmm on ha.cmmadjustmenttypeguid = cmm.cmmadjustmenttypeguid
join beebusiness hb on ha.beebusinessguid = hb.beebusinessguid and ha.probatchguid = hb.probatchguid
where cmm.adjustmenttypecode = 'RECRUIT' and ha.initialadjustmentamount != 0
group by hb.beenumber,ha.initialadjustmentamount )A)B
on B.beenumber = ltrim(rtrim(stuff(ea.sa_ss,1,patindex('%[^0]%', ea.sa_ss)-1,''))) where Ea.totalprom > 0
April 10, 2008 at 10:59 am
I don't think it can be treated as a full outer join because your WHERE clause doesn't allow for TOTALPROM to be null. the optimizer would then "override" your join and turn it into whatever it thought was appropriate (possibly INNER in this case).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2008 at 11:05 am
Matt,
Thanks for the heads up on the 'where' clause.
So when I delete the totalprom != 0.0, I get almost 2898 rows which once again looks like a cross join as Total row count cannot go beyond 203(91 + 112).
So what is my best bet then & how do I write me sql in here ?
April 10, 2008 at 11:13 am
upendramkumar (4/10/2008)
Matt,Thanks for the heads up on the 'where' clause.
So when I delete the totalprom != 0.0, I get almost 2898 rows which once again looks like a cross join as Total row count cannot go beyond 203(91 + 112).
So what is my best bet then & how do I write me sql in here ?
I was looking at the very last line (only).
Try changing:
where Ea.totalprom > 0
to
where Ea.totalprom > 0 OR ea.totalPROM is null
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2008 at 11:18 am
Matt,
When I added 'OR ea.totalPROM is null', it is fetching me 95 rows now.
I'm completely clueless !
April 10, 2008 at 11:21 am
You need to run the actual sub-query that you're using - it's not going to return the same as your query1. If you look - you will see that they are not at all the same.
Run the subquery on its own, and compare it to query1.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2008 at 11:29 am
Matt,
I guess what ever you are saying, isnt echoing me the result that I look for.
However I got what I needed by writing another outer query & calling the Query3 as P1 and deleted the 'where' clause completely.
Thanks a bunch for the 'where' clause info.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply