SUM() function brings wrong value with left join

Database

SUM() function brings wrong value with left join

I am creating a volatile table where I make a lot of left joins, and there's a SUM function, which is bringing a wrong value in the result. Look at the query:

 

CREATE MULTISET VOLATILE TABLE TAVATA_RECARGA_JUNHO AS (SELECT
A.ID_LNHA,A.ANOMES,A.FL_RECARGA,A.CANAL_RECARGA,A.FL_RECARGA_JUNHO AS FL_RECARGA_JUNHO,B.FL_RECARGA_JULHO AS FL_RECARGA_JULHO,C.FL_RECARGA_AGOSTO AS F_RECARGA_AGOSTO,D.FL_RECARGA_SETEMBRO AS FL_RECARGA_SETEMBRO,E.FL_RECARGA_OUTUBRO AS FL_RECARGA_OUTUBRO,F.FL_RECARGA_NOVEMBRO AS FL_RECARGA_NOVEMBRO,G.FL_RECARGA_DEZEMBRO AS FL_RECARGA_DEZEMBRO,A.VL_RCRG
FROM (SELECT ID_LNHA, ANOMES,FL_RECARGA, CANAL_RECARGA,FL_RECARGA_JUNHO, SUM(VL_RCRG) 
 AS VL_RCRG from RECARGA_PARQUE_JUNHO GROUP BY 1,2,3,4,5) AS A
LEFT JOIN (SELECT ID_LNHA, ANOMES,FL_RECARGA, CANAL_RECARGA,FL_RECARGA_JULHO, SUM(VL_RCRG) 
 AS VL_RCRG from RECARGA_PARQUE_JULHO GROUP BY 1,2,3,4,5) AS B
ON A.ID_LNHA = B.ID_LNHA
LEFT JOIN (SELECT ID_LNHA, ANOMES,FL_RECARGA, CANAL_RECARGA,FL_RECARGA_AGOSTO, SUM(VL_RCRG) 
 AS VL_RCRG from RECARGA_PARQUE_AGOSTO GROUP BY 1,2,3,4,5)AS C
ON A.ID_LNHA = C.ID_LNHA
LEFT JOIN (SELECT ID_LNHA, ANOMES,FL_RECARGA, CANAL_RECARGA,FL_RECARGA_SETEMBRO, SUM(VL_RCRG) 
 AS VL_RCRG from RECARGA_PARQUE_SETEMBRO GROUP BY 1,2,3,4,5) AS D
ON A.ID_LNHA = D.ID_LNHA
LEFT JOIN (SELECT ID_LNHA, ANOMES,FL_RECARGA, CANAL_RECARGA,FL_RECARGA_OUTUBRO, SUM(VL_RCRG) 
 AS VL_RCRG from RECARGA_PARQUE_OUTUBRO GROUP BY 1,2,3,4,5) AS E
ON A.ID_LNHA = E.ID_LNHA
LEFT JOIN (SELECT ID_LNHA, ANOMES,FL_RECARGA, CANAL_RECARGA,FL_RECARGA_NOVEMBRO, SUM(VL_RCRG) 
 AS VL_RCRG from RECARGA_PARQUE_NOVEMBRO GROUP BY 1,2,3,4,5) AS F
ON A.ID_LNHA = F.ID_LNHA
LEFT JOIN (SELECT ID_LNHA, ANOMES,FL_RECARGA, CANAL_RECARGA,FL_RECARGA_DEZEMBRO, SUM(VL_RCRG) 
 AS VL_RCRG from RECARGA_PARQUE_DEZEMBRO GROUP BY 1,2,3,4,5)AS G
ON A.ID_LNHA = G.ID_LNHA) WITH DATA PRIMARY INDEX (ID_LNHA) ON COMMIT PRESERVE ROWS;








 When I run the select below, I get the value 659.994.096,72.

 

SELECT SUM(VL_RCRGFROM RECARGA_PARQUE_JUNHO

 

But when I make the same query from the table TAVATA_RECARGA_JUNHO, I get 1.672.895.770,65.

Apparently, every left join I made multiplicates the values. Does anyone have any idea how I could write this query in a way I get the right value?

1 REPLY
Teradata Employee

Re: SUM() function brings wrong value with left join

You are doing a product join on each ID_LNHA.  Perhaps you need to add more qualifiers:

 

LEFT JOIN
(SELECT ID_LNHA ,ANOMES ,FL_RECARGA ,CANAL_RECARGA ,FL_RECARGA_JULHO ,SUM(VL_RCRG) AS VL_RCRG
from RECARGA_PARQUE_JULHO GROUP BY 1 ,2 ,3 ,4 ,5) AS B
 ON   A.ID_LNHA = B.ID_LNHA
 AND A.ANOMES = B.ANOMES  AND A.FL_RECARGA = B.FL_RECARGA  AND A.CANAL_RECARGA = B.CANAL_RECARGA  AND A.FL_RECARGA_JULHO = B.FL_RECARGA_JULHO

 

and so on for each outer join.