Using WITH Statement Modifier instead of Temp Tables?

Database

Using WITH Statement Modifier instead of Temp Tables?

I’ve run into some unknown territory here.  I have used a WITH statement modifier a number of times successfully.  I learned early on that the sequence is important when using two or three statements.  Now I have four WITH clauses I am using with each dependent on others.  I cannot get this to run no matter what sequence I place them in.  I’m sure I haven’t tried them all but I thought one of the SQL guru’s could provide some insight into how to do this. 

I have read the WITH Statement Modifier section in SQL DML Reference manuals but they have forgotten to discuss the importance of how the statements should be sequenced.  Here is my example.

WITH CNTC AS (
  SELECT
    GST_I
    , CNTC_D
    , ACCT_MO_END_D
    , CMPN_C
  FROM CMP_ADW.UA_GST_CNTC_CUV A
    INNER JOIN ADW.FSCL_CAL_DAY_SUV B
      ON A.CNTC_D = B.ACCT_D
  WHERE (A.CNTC_D BETWEEN '2012-07-01' AND '2012-07-15')
    AND SUBSTR(CELL_N, 1,3) <> ('NML')
),

GSTT AS (
  SELECT
    R.ALL_RFV_SEG_T
    , COUNT(DISTINCT C.GST_I) AS GSTMN
  FROM CNTC as C
    LEFT OUTER JOIN  adwtemp.gst_rfv_seg_v as R
      ON C.GST_I = R.GST_I
        AND C.ACCT_MO_END_D = R.SEG_YE_D
  WHERE (C.CNTC_D BETWEEN '2012-07-01' AND '2012-07-15')
  GROUP BY 1
),

RFVSEG AS (
  SELECT
    R.ALL_RFV_SEG_T
    , C.CMPN_C
    , COUNT(DISTINCT C.GST_I) AS GSTCOUNT
  FROM  CNTC as C
    LEFT OUTER JOIN  adwtemp.gst_rfv_seg_v as R
      ON C.GST_I = R.GST_I
        AND C.ACCT_MO_END_D = R.SEG_YE_D
  WHERE (C.CNTC_D BETWEEN '2012-07-01' AND '2012-07-15')
  GROUP BY 1, 2
),

GSTRFV AS (
  SELECT
    ALL_RFV_SEG_T
    , SUM (GSTCOUNT) AS RFVCNT
  FROM RFVSEG
  GROUP BY 1
)

SELECT
  RFVCNT
  , GSTMN
  , A.ALL_RFV_SEG_T
FROM GSTRFV as A
  INNER JOIN GSTT as B
    ON A.ALL_RFV_SEG_T = B.ALL_RFV_SEG_T ;

When this runs it compiles without any issue but I am getting an error message "Selekct Failed 3807:  Object 'RFVSEQ' does not exist."  Any help on this would be appreciated and if someone knows of some Teradata documentation on this subject, please step up to the plate.  I'm sure we would all appreciate it.

I believe this is a valid moethod to get past using volatile, derrived or global temp tables.

Thanks in advance for your help ...

5 REPLIES
Senior Apprentice

Re: Using WITH Statement Modifier instead of Temp Tables?

Believe it or not, but the implementation of multiple WITHs in Teradata is totally wrong.

There's an open DR (DR160077?) on that issue for a long time and the official workaround is to "reverse the order of the table expressions".

This means you have to write the CTEs bottom-up instead of top-down.

So this should work:

WITH 
GSTRFV AS (
SELECT
ALL_RFV_SEG_T
, SUM (GSTCOUNT) AS RFVCNT
FROM RFVSEG
GROUP BY 1
)
,
RFVSEG AS (
SELECT
R.ALL_RFV_SEG_T
, C.CMPN_C
, COUNT(DISTINCT C.GST_I) AS GSTCOUNT
FROM CNTC as C
LEFT OUTER JOIN adwtemp.gst_rfv_seg_v as R
ON C.GST_I = R.GST_I
AND C.ACCT_MO_END_D = R.SEG_YE_D
WHERE (C.CNTC_D BETWEEN '2012-07-01' AND '2012-07-15')
GROUP BY 1, 2
)
,
GSTT AS (
SELECT
R.ALL_RFV_SEG_T
, COUNT(DISTINCT C.GST_I) AS GSTMN
FROM CNTC as C
LEFT OUTER JOIN adwtemp.gst_rfv_seg_v as R
ON C.GST_I = R.GST_I
AND C.ACCT_MO_END_D = R.SEG_YE_D
WHERE (C.CNTC_D BETWEEN '2012-07-01' AND '2012-07-15')
GROUP BY 1
)
,
CNTC AS (
SELECT
GST_I
, CNTC_D
, ACCT_MO_END_D
, CMPN_C
FROM CMP_ADW.UA_GST_CNTC_CUV A
INNER JOIN ADW.FSCL_CAL_DAY_SUV B
ON A.CNTC_D = B.ACCT_D
WHERE (A.CNTC_D BETWEEN '2012-07-01' AND '2012-07-15')
AND SUBSTR(CELL_N, 1,3) <> ('NML')
)

SELECT
RFVCNT
, GSTMN
, A.ALL_RFV_SEG_T
FROM GSTRFV as A
INNER JOIN GSTT as B
ON A.ALL_RFV_SEG_T = B.ALL_RFV_SEG_T ;

If you're a customer please open an incident with Teradata support, I never loose hope they might finally fix it.

Re: Using WITH Statement Modifier instead of Temp Tables?

That's exactly what I did work from the bottom up.  It works with 2 and 3 with statements but in this case NOT.  Thanks for your input.

Enthusiast

Re: Using WITH Statement Modifier instead of Temp Tables?

How better is the CTE approach than a derived table .??Any recommendations when to use a CTE instead of a derived table. 

Enthusiast

Re: Using WITH Statement Modifier instead of Temp Tables?

Did DR160077 get resolved?

Senior Apprentice

Re: Using WITH Statement Modifier instead of Temp Tables?

AFAIK it will be resolved in TD16.