We help IT Professionals succeed at work.

star equal operator in implicit join

patd1
patd1 asked
on
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

Comment
Watch Question

Senior .Net Consultant
Top Expert 2016
Commented:
have you tried keeping the same order of tables:
FROM  dbo.me1 c
       LEFT OUTER JOIN  dbo.cmi1 b
                    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

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
Thank you.