Link to home
Start Free TrialLog in
Avatar of patd1
patd1Flag for United States of America

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:
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)

Open in new window


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)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
b.ulineID and a.ulineID are not the same columns as b.uID and a.uID.

Otherwise I don't see any ambiguities that could cause problems, as in some *= situations.
Avatar of patd1

ASKER

Thank you.