Bug - SQL Refactor prefixes the wrong table name

andy3180

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

Brian Donahue

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?

andy3180

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 Donahue

...and the refactoring used was qualify object names? Or was it a different refactoring?

andy3180

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