Earlier this week I started to having problems by using CTE on DirectQuery, as the error below. I didnt identify any syntax erron on the query, even because until last week it was ok.
-------------------------------------------------------CTE------------------------------------------------------------- WITH TMP_DSP_MIN AS ( SELECT BD798_ID_DATANB AS DMI_DT_MOVTO, COALESCE((SELECT CP392_VL_LIMITE FROM SFINANCECP..CP392 WHERE BD798_ID_DATANB >= CP392_DT_INICIO AND BD798_ID_DATANB <= CP392_DT_FIM AND CP392_TP_LIMITE = 5),0) AS DMI_VL_MOVTO --TROCAR ESTE PARAMETRO PARA 5 FROM DW..BD798 WITH(NOLOCK) WHERE SUBSTRING(CONVERT(VARCHAR,(BD798_ID_DATANB)),1,4)>= (SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE()),8,4)-2)), TMP_DFN_REA AS ( SELECT CP391_DT_MOVTO AS DFN_DT_MOVTOR, SUM(CP391_VL_MOVTO) AS DFN_VL_MOVTOR FROM SFINANCECP..CP391 WITH(NOLOCK) WHERE SUBSTRING(CONVERT(VARCHAR,(CP391_DT_MOVTO)),1,4)>= (SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE()),8,4)-2) AND CP391_ID_STATUS = 1 AND CP391_TP_MOVTO = 1 AND CP391_CD_CONTA IN (SELECT CP393_CD_CONTA FROM SFINANCECP..CP393 WITH(NOLOCK) WHERE CP393_TP_FLUXO = 1 AND CP391_DT_MOVTO >= CP393_DT_INICIO AND CP391_DT_MOVTO <= CP393_DT_FIM ) GROUP BY CP391_DT_MOVTO ), TMP_DFN_PRJ AS ( SELECT CP391_DT_MOVTO AS DFN_DT_MOVTOP, SUM(CP391_VL_MOVTO) AS DFN_VL_MOVTOP FROM SFINANCECP..CP391 WITH(NOLOCK) WHERE SUBSTRING(CONVERT(VARCHAR,(CP391_DT_MOVTO)),1,4)>= (SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE()),8,4)-2) AND CP391_ID_STATUS = 1 AND CP391_TP_MOVTO = 2 AND CP391_CD_CONTA IN (SELECT CP393_CD_CONTA FROM SFINANCECP..CP393 WITH(NOLOCK) WHERE CP393_TP_FLUXO = 1 AND CP391_DT_MOVTO >= CP393_DT_INICIO AND CP391_DT_MOVTO <= CP393_DT_FIM ) GROUP BY CP391_DT_MOVTO ), TMP_DSP_MED AS ( SELECT CP394_DT_REF AS DME_DT_REF, CP394_VL_MEDIA AS DME_VL_MEDIA FROM SFINANCECP..CP394 WITH(NOLOCK) WHERE CP394_TP_FLUXO = 1 AND SUBSTRING(CONVERT(VARCHAR,(CP394_DT_REF)),1,4)>= (SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE()),8,4)-2)) ---------------------------------------------------------------------------------------------------------------------- SELECT CAST(CAST(CAST(DMI_DT_MOVTO AS INT)AS CHAR(8))AS DATE) AS DT_MOVTO, CASE WHEN COALESCE(DMI_VL_MOVTO ,0) = 0 THEN 0 ELSE DMI_VL_MOVTO END AS VL_DIPMIN, CASE WHEN COALESCE(DFN_VL_MOVTOR,0) = 0 THEN 0 ELSE DFN_VL_MOVTOR END AS VL_DSPFRE, CASE WHEN COALESCE(DFN_VL_MOVTOP,0) = 0 THEN 0 ELSE DFN_VL_MOVTOP END AS VL_DSPFPJ, CASE WHEN COALESCE(DME_VL_MEDIA ,0) = 0 THEN 0 ELSE DME_VL_MEDIA END AS VL_DSPFMD FROM TMP_DSP_MIN WITH(NOLOCK) LEFT JOIN TMP_DFN_REA WITH(NOLOCK) ON DMI_DT_MOVTO = DFN_DT_MOVTOR LEFT JOIN TMP_DFN_PRJ WITH(NOLOCK) ON DMI_DT_MOVTO = DFN_DT_MOVTOP LEFT JOIN TMP_DSP_MED WITH(NOLOCK) ON DMI_DT_MOVTO = DME_DT_REF WHERE SUBSTRING(CONVERT(VARCHAR,(DMI_DT_MOVTO)),1,4)>= (SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE()),8,4)-2) AND SUBSTRING(CONVERT(VARCHAR,(DMI_DT_MOVTO)),1,8)<= (SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,8))
Can someone help me?
Solved! Go to Solution.
You may take a look at the following posts.
You may take a look at the following posts.
I identified something else as well..
When I open the file and click refresh, all query works without problems. However, when I open Power Query and edit the query by doing just one copy / paste and try again to update, the error happens. Is this a version bug?refresh - before editing query doing copy/paste
refresh - after editing query doing copy/paste
User | Count |
---|---|
135 | |
59 | |
56 | |
55 | |
47 |
User | Count |
---|---|
130 | |
73 | |
54 | |
54 | |
50 |