How to JOIN where the identifier could not be associated

Question

I have the following query ready on an old system:

SELECT regional, r.cod_gerente AS cod_gerente, 
    u2.username AS nome_gerente, 
    s.COD_REPRESENTANTE AS cod_representante, 
    u1.username AS nome_representante, 
    SUM(s.jan_valor) AS valor_total_seca, 0 as valor_total_liquida 
    FROM USUARIOS U1, USUARIOS U2, USUARIOS_USUARIOS R, cubo s 

    WHERE s.COD_GRUPO IN (1) 
    AND s.ANO = year(getdate()) 
    AND U1.COD_SISTEMA > 0 
    AND R.COD_REPRESENTANTE = U1.COD_SISTEMA 
    AND U2.COD = R.COD_GERENTE 
    AND U2.SUB_GERENTE = 1 
    AND (s.cod_representante = U1.COD_SISTEMA) 
    AND (PATINDEX('%' + CAST(s.cod_grupo AS NVARCHAR(MAX)) + '%', U2.GRUPOS) > 0 
        AND PATINDEX('%' + ( (SELECT DISTINCT CAST(cod_canal AS NVARCHAR(MAX)) 
                                FROM clientes 
                                WHERE cod_cliente = s.cod_cliente)) + '%', u2.canais) > 0) 
    GROUP BY regional, r.cod_gerente, u2.username, s.COD_REPRESENTANTE, u1.username, u2.grupos 

I need now to select the matricula_usuario on the table tab_comissoes according to the column cod_usuario table usuarios. I made a LEFT JOIN:

SELECT tab.matricula_usuario as tabCodigo , regional, r.cod_gerente AS cod_gerente, 
    u2.username AS nome_gerente, 
    s.COD_REPRESENTANTE AS cod_representante, 
    u1.username AS nome_representante, 
    SUM(s.jan_valor) AS valor_total_seca, 0 as valor_total_liquida 
    FROM USUARIOS U1, USUARIOS U2, USUARIOS_USUARIOS R, cubo s 
    LEFT JOIN tab_comissoes as tab ON  tab.cod_usuario = U2.cod 

    WHERE s.COD_GRUPO IN (1) 
    AND s.ANO = year(getdate()) 
    AND U1.COD_SISTEMA > 0 
    AND R.COD_REPRESENTANTE = U1.COD_SISTEMA 
    AND U2.COD = R.COD_GERENTE 
    AND U2.SUB_GERENTE = 1 
    AND (s.cod_representante = U1.COD_SISTEMA) 
    AND (PATINDEX('%' + CAST(s.cod_grupo AS NVARCHAR(MAX)) + '%', U2.GRUPOS) > 0 
        AND PATINDEX('%' + ( (SELECT DISTINCT CAST(cod_canal AS NVARCHAR(MAX)) 
                                FROM clientes 
                                WHERE cod_cliente = s.cod_cliente)) + '%', u2.canais) > 0) 
    GROUP BY regional, r.cod_gerente, u2.username, s.COD_REPRESENTANTE, u1.username, u2.grupos

But I get the error:

[Error Code: 4104, SQL State: S1000] The multi-part identifier "U2.Cod" could not be associated.

Answer 1

Do not mix ANSI syntax with simplified syntax. SQL Server gets lost.

Use as follows:

SELECT tab.matricula_usuario as tabCodigo , regional, r.cod_gerente AS cod_gerente, 
    u2.username AS nome_gerente, 
    s.COD_REPRESENTANTE AS cod_representante, 
    u1.username AS nome_representante, 
    SUM(s.jan_valor) AS valor_total_seca, 0 as valor_total_liquida 
    FROM USUARIOS U1
    INNER JOIN USUARIOS_USUARIOS R ON (R.COD_REPRESENTANTE = U1.COD_SISTEMA)
    INNER JOIN USUARIOS U2 ON (U2.COD = R.COD_GERENTE)
    INNER JOIN cubo s ON (s.cod_representante = U1.COD_SISTEMA)
    LEFT JOIN tab_comissoes as tab ON tab.cod_usuario = U2.cod 

    WHERE s.COD_GRUPO IN (1) 
    AND s.ANO = year(getdate()) 
    AND U1.COD_SISTEMA > 0 
    AND U2.SUB_GERENTE = 1 
    AND (PATINDEX('%' + CAST(s.cod_grupo AS NVARCHAR(MAX)) + '%', U2.GRUPOS) > 0 
        AND PATINDEX('%' + ( (SELECT DISTINCT CAST(cod_canal AS NVARCHAR(MAX)) 
                                FROM clientes 
                                WHERE cod_cliente = s.cod_cliente)) + '%', u2.canais) > 0) 
    GROUP BY tab.matricula_usuario, regional, r.cod_gerente, u2.username, s.COD_REPRESENTANTE, u1.username, u2.grupos