Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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