patd1
asked on
star equal operator in implicit join
I have code in my stored proc that is using an implicit join along with *= operator, which I am converting to explicit join and removing *= operator. Please suggest what is wrong in the following code conversion, as I am not getting the same results from both queries.
existing code:
converted code:
existing code:
SELECT c.policy,
c.edition,
Isnull(b.ulineID, a.ulineID) AS ulineid,
Isnull(b.dID, a.dID) AS did ,
Sum(Isnull(a.prior_unit_count, 0)) AS prior_unit_count,
Sum(Isnull(b.current_unit_count, 0)) AS current_unit_count
INTO dbo.uc1
FROM dbo.pmi1 a,
dbo.cmi1 b,
dbo.me1 c
WHERE c.policy *= b.policy
AND c.ulineID *= b.ulineID
AND c.policy *= a.policy
AND c.ulineID *= a.ulineID
GROUP BY c.policy,
c.edition,
Isnull(b.ulineID, a.ulineID),
Isnull(b.dID, a.dID)
ORDER BY c.policy,
c.edition,
Isnull(b.ulineID, a.ulineID),
Isnull(b.dID, a.dID)
converted code:
SELECT c.policy,
c.edition,
Isnull(b.uID, a.uID) AS uid,
Isnull(b.dID, a.dID) AS did ,
Sum(Isnull(a.prior_unit_count, 0)) AS prior_unit_count,
Sum(Isnull(b.current_unit_count, 0)) AS current_unit_count
INTO dbo.uc1
FROM dbo.cmi1 b
LEFT OUTER JOIN dbo.me1 c
ON c.policy = b.policy
AND c.uID = b.uID
LEFT OUTER JOIN dbo.pmi1 a
ON c.policy = a.policy
AND c.uID = a.uID
GROUP BY c.policy,
c.edition,
Isnull(b.uID, a.uID),
Isnull(b.dID, a.dID)
ORDER BY c.policy,
c.edition,
Isnull(b.uID, a.uID),
Isnull(b.dID, a.dID)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
Otherwise I don't see any ambiguities that could cause problems, as in some *= situations.