Competition: What’s your favorite Redgate tool? Enter now.

Bug - SQL Refactor prefixes the wrong table name

andy3180andy3180 Posts: 9
edited May 21, 2007 12:42PM in SQL Refactor Previous Versions
Here is the case where it fails. SQL Refactor's change is highlighted in bold -

DECLARE @resultTab TABLE(StateId TINYINT, StateDescription VARCHAR(20), Count800 INT,
Count888 INT, Count877 INT, Count866 INT, CountOther INT, countAll INT)

INSERT INTO @resultTab
SELECT CALL_DnisStates.StateId, CALL_DnisStates.StateDescription, 0, 0, 0, 0, 0, 0
FROM dbo.CALL_DnisStates WITH (NOLOCK)
ORDER BY CALL_DnisStates.stateid ASC

-- For each prefix, get a count of dnis numbers grouped by stateid and update @resultTab

-- 800 numbers
UPDATE @resultTab
SET A.Count800 = A.Count800
FROM
@resultTab R
INNER JOIN (
SELECT ci.stateid, Count800 = COUNT(ci.stateid)
FROM dbo.CALL_IncomingDNIS ci WITH (NOLOCK)
WHERE ci.dnis LIKE '800%'
GROUP BY ci.stateId
) A ON A.StateId = R.StateId

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Andy,

    Sorry, I'm not sure I understand. What was the SQL before refactoring, and which refactoring was used to come up with this result?
  • Before refactoring, the code was -

    ...
    UPDATE @resultTab
    SET Count800 = A.Count800
    FROM
    @resultTab R
    INNER JOIN (
    SELECT ci.stateid, Count800 = COUNT(ci.stateid)
    FROM dbo.CALL_IncomingDNIS ci WITH (NOLOCK)
    WHERE ci.dnis LIKE '800%'
    GROUP BY ci.stateId
    ) A ON A.StateId = R.StateId


    And after refactoring it is -

    ...
    UPDATE @resultTab
    SET A.Count800 = A.Count800
    FROM
    @resultTab R
    INNER JOIN (
    SELECT ci.stateid, Count800 = COUNT(ci.stateid)
    FROM dbo.CALL_IncomingDNIS ci WITH (NOLOCK)
    WHERE ci.dnis LIKE '800%'
    GROUP BY ci.stateId
    ) A ON A.StateId = R.StateId
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    ...and the refactoring used was qualify object names? Or was it a different refactoring?
  • It was used to qualify object names but the tool qualified the LHS Count800 wrongly. The LHS Count800 is a column in the @resultTab TABLE (not on A - the result of a SELECT).
Sign In or Register to comment.