Common Table Expressions are not consumable in Report

Database
Highlighted

Common Table Expressions are not consumable in Report

Hello,

Unable to use Teradata Common Table Expressions (CTEs) in Tableau Custom SQL and needs to be rewritten.

And attached the Query. Could you please help on what are my options?

WITH 
KPI AS ( 
	SELECT * FROM (SELECT CAST('x' AS VARCHAR(2)) AS A, CAST('x' AS VARCHAR(30)) AS B, CAST('x' AS VARCHAR(30)) AS C) S 
	UNION ALL
	SELECT 'A' AS A, 'ECE Metrics' AS B, TO_CHAR(D.SDT, 'MM/DD/YYYY') AS C FROM D 
		UNION 
	SELECT 'W' AS A, 'OTHER_TOTAL' AS B, NVL(TO_CHAR(SUM(CNT)),'0') AS C FROM ECE_METRICS 
	WHERE ITEM_TYPE NOT IN ('ECE', 'MOBILEFIRST', 'CHATBOT_ASKVZ', 'CHATBOT_ASKVZECE') OR ITEM_TYPE IS NULL
), 

KPI_METRICS_CNTS AS (
    SELECT '3' AS A, (A.ITEM_TYPE)  AS B,
           TO_CHAR(NVL(SUM(A.CNT), 0)) AS C,
           TO_CHAR(NVL(SUM(B.CNT), 0)) AS D,
           TO_CHAR(NVL(SUM(D.CNT), 0)) AS F,
           TO_CHAR(NVL(SUM(E.CNT), 0)) || ' s' AS G
    FROM KPI_METRIC A 
	LEFT JOIN KPI_METRICS_SALE B ON (A.ITEM_ID || ' ' || A.ITEM_TYPE) = (B.ITEM_ID || ' ' || B.ITEM_TYPE)
    LEFT JOIN KPI_METRICS_SALEPENDINGC ON (A.ITEM_ID || ' ' || A.ITEM_TYPE) = (C.ITEM_ID || ' ' || C.ITEM_TYPE)
    LEFT JOIN KPI_METRICS_SALEDROP D ON (A.ITEM_ID || ' ' || A.ITEM_TYPE) = (D.ITEM_ID || ' ' || D.ITEM_TYPE)
    GROUP BY '3', A.ITEM_TYPE 
), 

KPI_METRICS_AVG_CHAT_LENGTH AS (
    SELECT ITEM_ID, ITEM_TYPE, SUM(CNT) AS CNT 
    FROM ((SELECT ITEM_ID, ITEM_TYPE, ROUND(((SUM(CHAT_LENGTH_SEC)/COUNT(ITEM_ID))/60), 2) AS CNT 
          FROM (SELECT A.ITEM_ID, A.ITEM_TYPE, A.ITEM_ID, 
                       CAST(EXTRACT(HOUR FROM DIFF) * 60 * 60 + 
                            EXTRACT(MINUTE FROM DIFF) * 60 + 
                            EXTRACT(SECOND FROM DIFF) AS DECIMAL(10,3)) AS CHAT_LENGTH_SEC 
                FROM (SELECT A.ITEM_ID, A.ITEM_TYPE, A.ITEM_ID, 
                             (MAX(A.CREATE_DATE) OVER (PARTITION BY A.ITEM_ID) - 
                              MIN(A.CREATE_DATE) OVER (PARTITION BY A.ITEM_ID)) DAY(4) TO SECOND AS DIFF                       
       FROM Teradata.SALE_INFOTable A 
                      JOIN KPI_METRICS_SESS B ON A.ITEM_ID = B.ITEM_ID) A) A 
          GROUP BY ITEM_ID, ITEM_TYPE)
          UNION
         (SELECT ITEM_ID, ITEM_TYPE, 0 AS CNT FROM KPI_METRICS_SESS)) A 
    GROUP BY ITEM_ID, ITEM_TYPE 
), 

KPI_METRICS_SALEDROP AS (
    SELECT ITEM_ID, ITEM_TYPE, SUM(CNT) AS CNT 
	FROM ((SELECT ITEM_ID, ITEM_TYPE, (T - (S + E)) AS CNT 
	       FROM (SELECT A.ITEM_ID, A.ITEM_TYPE, A.CNT AS T, SUM(NVL(B.CNT, 0)) AS S, SUM(NVL(C.CNT, 0)) AS E 
v                 FROM KPI_METRICS A 
				 LEFT JOIN KPI_METRICS_SALE B ON (A.ITEM_ID || ' ' || A.ITEM_TYPE) = (B.ITEM_ID || ' ' || B.ITEM_TYPE)
                 LEFT JOIN KPI_METRICS_SALEPENDINGC ON (A.ITEM_ID || ' ' || A.ITEM_TYPE) = (C.ITEM_ID || ' ' || C.ITEM_TYPE)
                 GROUP BY A.ITEM_ID, A.ITEM_TYPE, A.CNT) A)
           UNION
          (SELECT ITEM_ID, ITEM_TYPE, 0 AS CNT FROM KPI_METRICS_SESS)) A 
    GROUP BY ITEM_ID, ITEM_TYPE 
), 

KPI_METRICS_SALEPENDINGS (
    SELECT ITEM_ID, ITEM_TYPE, SUM(CNT) AS CNT 
	FROM ((SELECT A.ITEM_ID, A.ITEM_TYPE, COUNT(DISTINCT A.ITEM_ID) AS CNT
           FROM KPI_METRICS_SaleDrop_RAW A 
           GROUP BY A.ITEM_ID, A.ITEM_TYPE)
           UNION
          (SELECT ITEM_ID, ITEM_TYPE, 0 AS CNT FROM KPI_METRICS_SESS)) A 
    GROUP BY ITEM_ID, ITEM_TYPE 
), 

KPI_METRICS_SALE AS (
    SELECT ITEM_ID, ITEM_TYPE, SUM(CNT) AS CNT 
	FROM ((SELECT ITEM_ID, ITEM_TYPE, COUNT(DISTINCT A.ITEM_ID) AS CNT 
           FROM ((SELECT ITEM_ID, ITEM_TYPE, ITEM_ID FROM KPI_METRICS_SALE_RAW
                  MINUS 
                  SELECT ITEM_ID, ITEM_TYPE, ITEM_ID FROM KPI_METRICS_SaleDrop_RAW)) A 
           GROUP BY ITEM_ID, ITEM_TYPE) 
           UNION
          (SELECT DISTINCT ITEM_ID, ITEM_TYPE, 0 AS CNT FROM KPI_METRICS_SESS)) A 
    GROUP BY ITEM_ID, ITEM_TYPE 
), 

KPI_METRICS_SaleDrop_RAW AS (
    SELECT DISTINCT A.ITEM_ID, A.ITEM_TYPE, A.ITEM_ID 
   FROM Teradata.SALE_INFOTable A 
	JOIN KPI_METRICS_SESS B ON A.ITEM_ID = B.ITEM_ID
    WHERE (
		
			A.SALE_INFO LIKE ('%SALE_INFOECE%') OR 
			A.SALE_INFO LIKE ('%SALE_INFOECE_HEX_LIVE%') 
        )
), 
KPI_METRICS_SALE_RAW AS (
    SELECT DISTINCT A.ITEM_ID, A.ITEM_TYPE, A.ITEM_ID 
    FROM D, Teradata.SALE_INFOTable A 
	JOIN KPI_METRICS_SESS B ON A.ITEM_ID = B.ITEM_ID
    WHERE (
		-- CM
			A.SALE_INFO LIKE ('%SALE_INFOCHANGE_MDN_SALE%') OR
			A.SALE_INFO LIKE ('%SALE__PAYMENT_SHOW_STORE%') OR
			A.SALE_INFO LIKE ('%SALE__ELIGIBLE%') OR
) 
        )
), 

KPI_METRICS AS (
    (SELECT A.ITEM_ID, A.ITEM_TYPE, COUNT(DISTINCT A.ITEM_ID) AS CNT
    FROM D, Teradata.SALE_INFOTable A 
	JOIN KPI_METRICS_SESS B ON A.ITEM_ID = B.ITEM_ID
    GROUP BY A.ITEM_ID, A.ITEM_TYPE)
), 

KPI_METRICS_SESS AS (
    SELECT DISTINCT A.ITEM_ID, A.ITEM_TYPE, A.ITEM_ID
    FROM SLI, D, Teradata.SALE_INFOTable A
    WHERE A.CREATE_DATE_EST BETWEEN D.SDT AND D.EDT 
    AND SLI.ITEM_CODE = D.ITEM_CODE 
    AND A.SECOND_LEVEL_INTENT = SLI.SLI_VAL ), 
             
SALEITEMS AS (
SELECT * FROM (SELECT CAST('x' AS VARCHAR(100)) AS ITEM_CODE, CAST('x' AS VARCHAR(100)) AS SLI_VAL) S UNION ALL
SELECT * FROM (SELECT 'ChangeITEMs' AS ITEM_CODE, 'SALE_INFOCHANGE_MDN' AS SLI_VAL) S UNION ALL
SELECT * FROM (SELECT 'ITEMPlan' AS ITEM_CODE, 'SALE_INFOECE_ENTRY' AS SLI_VAL) S UNION ALL 
SELECT * FROM (SELECT 'ManageITEMs' AS ITEM_CODE, 'SALE_INFOECE_PS_MA_START' AS SLI_VAL) S
), 


D AS (SELECT TO_TIMESTAMP('startDate', 'YYYY-MM-DD HH24:MI:SS') AS SDT,
             TO_TIMESTAMP('endDate', 'YYYY-MM-DD HH24:MI:SS') AS EDT, 
             'reportType' AS ITEM_CODE)

SELECT B, C FROM RESULTS WHERE A <> 'x' ORDER BY A;