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

11 REPLIES
Junior Contributor

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. 

Junior Supporter

Re: Using WITH Statement Modifier instead of Temp Tables?

Did DR160077 get resolved?

Junior Contributor

Re: Using WITH Statement Modifier instead of Temp Tables?

AFAIK it will be resolved in TD16.

Enthusiast

Re: Using WITH Statement Modifier instead of Temp Tables?

BUMP

Hi has ticket DR160077 been resolved yet? It's been a year since it's been reported on this thread and I'm still running into the same issues.

Teradata Employee

Re: Using WITH Statement Modifier instead of Temp Tables?

It was fixed in Teradata 16.0, along with allowing CTEs in views.  The order in which CTEs are listed should not matter anymore.

Enthusiast

Re: Using WITH Statement Modifier instead of Temp Tables?

Hi @GJColeman,

 

Thanks for  getting back so fast. I'm currently running TD v16.10.01.

 

When I run my CTE in ascending (top-down) order, it spits back the error: "Object '<CTE1>' does not exist". HOWEVER, if I was to flip the order with CTE2 on top (WITH CTE2 AS...), then it runs the query no problem. This could potentially be a non-related issue, but I have a gut feeling it's CTE order related. 

Teradata Employee

Re: Using WITH Statement Modifier instead of Temp Tables?

Yep.  I thought that was supposed to be changed so order did not matter.  You could open an incident on that and mention DR 177575.