Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Gabrielfranco
Frequent Visitor

Error by using CTE (WITH)

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.

ERRO.JPG

-------------------------------------------------------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?

 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Gabrielfranco ,

 

You may take a look at the following posts.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Gabrielfranco ,

 

You may take a look at the following posts.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gabrielfranco
Frequent Visitor

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/pasterefresh - before editing query doing copy/paste

 

refresh - after editing query doing copy/pasterefresh - after editing query doing copy/paste

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors