All Forums Database
flsandman 2 posts Joined 03/11
19 May 2015
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 ...

dnoeth 4628 posts Joined 11/04
19 May 2015

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.

Dieter

flsandman 2 posts Joined 03/11
19 May 2015

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.

drmkd17 54 posts Joined 10/12
21 Jul 2016

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

teradatatester 69 posts Joined 01/10
31 Aug 2016

Did DR160077 get resolved?

dnoeth 4628 posts Joined 11/04
01 Sep 2016

AFAIK it will be resolved in TD16.

Dieter

You must sign in to leave a comment.