Error by using CTE (WITH)

[Gabrielfranco]

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?

[v-chuncz-msft · Accepted solution]

@Gabrielfranco ,

You may take a look at the following posts.

• https://community.powerbi.com/t5/Desktop/Custom-query-with-CTE-not-supported-bug/m-p/41395#M15659
• https://community.powerbi.com/t5/Desktop/How-to-DirectQuery-View-with-Recursive-Query-CTE/m-p/408831...

[Gabrielfranco]

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