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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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