T-SQL - Delete filter "NOT IN" does not work

Question

I am filtering my base by store code (varchar), but the filter is not removing the store codes I want to delete from my query. This is my consultation:

SELECT A.CPF
     , A.[Cod Loja]
     , B.NomeLoja
     , A.Data_Cadastro
INTO Cadastro
FROM Tabela_Geral A
LEFT JOIN Tabela_Lojas B
ON A.[Cod Loja] = (B.[Cod Loja]*1)  
AND B.NomeLoja <> ''
WHERE A.Data_Cadastro <= @FIM_MES
AND A.[COD LOJA] NOT IN ('5100', '5106', '5107', '5108', '5109', '5110', '5113', '5080')

Does anyone have any idea what might be going on for the NOT IN filter not to work? My query brings the stores I Filtreed out.

Answer 1

By multiplying B.[Cod Loja] by 1, the value of B.[Cod Loja] is automatically converted from text value to numeric value. This forces A.[Cod Loja] also be transformed into a numerical value to make the junction defined in clause ON. It is the same thing as

ON cast (A.[Cod Loja] as int) = cast (B.[Cod Loja] as int)

The details are in the article The dangers of automatic data type conversion.

About (B.[Cod Loja]*1) you commented that "It’s a legacy code, asked not to change". In this case, since the ON clause is forcing the conversion of A.[Cod Loja] for numeric value, then the suggestion is that in the WHERE clause you do manual conversion of A.[COD LOJA] for numeric value and then compare with numeric values. Something like this:

-- código #1
SELECT A.CPF
     , A.[Cod Loja]
     , B.NomeLoja
     , A.Data_Cadastro
--INTO Cadastro
  FROM Tabela_Geral A
       LEFT JOIN Tabela_Lojas B
          ON A.[Cod Loja] = (B.[Cod Loja]*1)  
             AND B.NomeLoja <> ''
  WHERE A.Data_Cadastro <= @FIM_MES
        AND cast (A.[COD LOJA] as int) NOT IN (5100, 5106, 5107, 5108, 5109, 5110, 5113, 5080);

---

Since the column with the store code can have 3 or 4 characters, here is SQL code for you to check the quality of the column data [Cod Loja] of Tabela_Geral:

-- código #2 v3
SELECT CPF, [Cod Loja], len ([Cod Loja]) as Tamanho, Data_Cadastro
  from Tabela_Geral
  where (len([Cod Loja]) < 3) or (len([Cod Loja]) > 4);

and

-- código #3
SELECT len ([Cod Loja]), count(*) as Qtd
  from Tabela_Geral
  group by len ([Cod Loja]);