Excel ODBC SQL query doesn't bring updated data on Refresh

Question

I've created a ODBC query in SQL retrieving data from Other Excel sources and making a JOIN. The query runs fine on the first time, but if I try to update the data source and then Refresh the query, it doesn't generate any erro, but doesn't bring the updated figures either.

This problem started to happen since I've added the LEFT JOIN clause to the query.

Any ideas on how to solve it?

below the query code:

SELECT A.Type, B.Product, B.Active Ingredient, B.Category, A.VPC_Group, Max(A.Portfolio1) as Portfolio1, sum(A.'Qty1') as 'Qty1', sum(A.'NS1') as 'NS1', sum(A.'GP1') as 'GP1', sum(A.'NSLC1') as 'NSLC1', Max(A.Portfolio2) as Portfolio2, sum(A.'Qty2') as 'Qty2', sum(A.'NS2') as 'NS2', sum(A.'GP2') as 'GP2', sum(A.'NSLC2') as 'NSLC2', Max(A.Portfolio3) as Portfolio3, sum(A.'Qty3') as 'Qty3', sum(A.'NS3') as 'NS3', sum(A.'GP3') as 'GP3', sum(A.'NSLC3') as 'NSLC3'

FROM (

SELECT Type, Code Norm, VPC_Group, Max(PF1) as Portfolio1, Sum(Qty1) AS 'Qty1', Sum(NS1) AS 'NS1', Sum(GP1) AS 'GP1', Sum(NSLC1) as 'NSLC1', Max(PF2) as Portfolio2, Sum(Qty2) as 'Qty2', Sum(NS2) as 'NS2', Sum(GP2) as 'GP2', Sum(NSLC2) as 'NSLC2', Max(PF3) as Portfolio3, Sum(Qty3) as 'Qty3', Sum(NS3) as 'NS3', Sum(GP3) as 'GP3', Sum(NSLC3) as 'NSLC3'

FROM (

SELECT Type, Code Norm, IIF(Type='Indent','Indent', IIF(Area code = '500', 'FA', 'w/o FA')) as VPC_Group, null as PF1, 0 as Qty1, 0 as NS1, 0 as GP1, 0 as NSLC1, null as PF2, 0 as Qty2, 0 as NS2, 0 as GP2, 0 as NSLC2, Portfolio as PF3, Qty as Qty3,NS as NS3, GM PIMS as GP3, NS_BRL as NSLC3

FROM C:\Users\mzsampa\Company\Brazil-LDN-Controladoria-INT - Documents\General\Projetos\Modelo_Resultado\Sales\SM_Database - 2023.xlsm.Tabela Tabela

WHERE (Version = 'RFC 2023 05 V01') AND (Type IN ('Third Party', 'Indent', 'IFRS 15')) AND (Year = 2023)

UNION ALL

SELECT Type, Code Norm, IIF(Type='Indent','Indent', IIF(Area code = '500', 'FA', 'w/o FA')) as VPC_Group, null as PF1, 0 as Qty1, 0 as NS1, 0 as GP1, 0 as NSLC1, Portfolio as PF2, Qty as Qty2, NS as NS2, GM PIMSas GP2, NS_BRL as NSLC2, null as PF3, 0 as Qty3, 0 as NS3, 0 as GP3, 0 as NSLC3

FROM C:\Users\mzsampa\Company\Brazil-LDN-Controladoria-INT - Documents\General\Projetos\Modelo_Resultado\Sales\SM_Database - 2023.xlsm.Tabela Tabela

WHERE (Version = 'RFC 2023 04 v02') AND (Type IN ('Third Party', 'Indent', 'IFRS 15')) AND (Year = 2023)

UNION ALL

SELECT Type, Code Norm, IIF(Type='Indent','Indent', IIF(Area code = '500', 'FA', 'w/o FA')) as VPC_Group, Portfolio as PF1, Qty as Qty1, NS as NS1, GM PIMS as GP1, NS_BRL as NSLC1, null as PF2, 0 as Qty2, 0 as NS2, 0 as GP2, 0 as NSLC2, null as PF3, 0 as Qty3, 0 as NS3, 0 as GP3, 0 as NSLC3

FROM C:\Users\mzsampa\Company\Brazil-LDN-Controladoria-INT - Documents\General\Projetos\Modelo_Resultado\Sales\SM_Database - 2023.xlsm.Tabela Tabela

WHERE (Version = 'WP 2023 v11') AND (Type IN ('Third Party', 'Indent', 'IFRS 15')) AND (Year = 2023)

)

GROUP BY Type, Code Norm, VPC_Group) as A

LEFT JOIN

(SELECT Extranet Items Name as Product, Extranet A#I#Name as Active Ingredient, Cód_Material, Category

FROM C:\Users\mzsampa\Company\Brazil-LDN-Controladoria-INT - Documents\General\Projetos\Modelo_Resultado\DE_PARA_PRODUTOS_REVISADO_V03.XLSM.de_para_prods) as B

ON (A.Code Norm = B.Cód_Material)

GROUP BY A.Type, B.Product, B.Active Ingredient, B.Category, A.VPC_Group

Answer 1

For Excel ODBC SQL query code issue, the best place for asking related queries is to Newest 'SQL' Questions - Stack Overflow.

You can post the question there for assistance on this.