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 now need to select the matricula_usuario
in the tab_comissoes
table according to the cod_usuario
column of the usuarios
table. 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.