"WITH RECURSIVE" CONCATENATION RESULT TRUNCATED

Database
Enthusiast

"WITH RECURSIVE" CONCATENATION RESULT TRUNCATED

Dear all, 

I am trying to concatenate multiple rows with a commun entry in one new column using the "WITH RECURSIVE" syntax. But I have noticed that the result, when  exceeding a certain length, is truncated. I have tried to modify the column length using the CAST function but it doesn't work, probably because it's a temporary table... Is there any solution to tell Teradata to change the default column size and display the whole result? Here is a code snippet summarizing my code logic:

-- CREATE A VOLATILE TABLE TOTO
CREATE VOLATILE TABLE TOTO
AS
(SELECT TT.* ,
rank() over (partition by TT.IDNT_EXTR_SRV order by TT.SEQ ASC) as SEQQ
FROM
(SELECT
TSAV.IDNT_COMP_SERV,
ACTIVATION_LIGNE.IDNT_EXTR_SRV,
ACTIVATION_LIGNE.IDNT_EXTR_OFR,
ACTIVATION_LIGNE.DAT_HEUR_ACTV,
OFFRE.LIBL_OFFR,
TSAV.IDNT_INTR_TCK_CLT,
TSAV.NUMR_EXTR_TCK_CLT,
TSAV.DAT_HEUR_OUVR_TCK_CLT,
SAV_STATUS.DAT_HEUR_CLT_ACT,
SAV_STATUS.RANG_CLT_ACT_ASCN AS SEQ,
SAV_ETAPE.LIBL_ETP,
RANK() over (partition by ACTIVATION_LIGNE.IDNT_EXTR_SRV order by ACTIVATION_LIGNE.DAT_HEUR_ACTV DESC) as rnk
FROM EXPERT_PRD.OF_SAV_FD_HIST_TCK_CLT TSAV
INNER JOIN EXPERT_PRD.OF_ACF_FD_ACTIVATION ACTIVATION_LIGNE
ON TSAV.IDNT_COMP_SERV = ACTIVATION_LIGNE.IDNT_COMP_SERV
AND ACTIVATION_LIGNE.DAT_HEUR_ACTV <= TSAV.DAT_HEUR_OUVR_TCK_CLT
INNER JOIN EXPERT_PRD.OF_ROF_RN_DETL_OFFR OFFRE
ON OFFRE.IDNT_OFFR = ACTIVATION_LIGNE.IDNT_EXTR_OFR
LEFT OUTER JOIN EXPERT_PRD.OF_SAV_FD_HIST_TCK_CLT_ACTION SAV_STATUS
ON TSAV.IDNT_INTR_TCK_CLT = SAV_STATUS.IDNT_INTR_TCK_CLT
LEFT OUTER JOIN EXPERT_PRD.OF_SAV_RN_TCK_ETAPE SAV_ETAPE
ON SAV_ETAPE.IDNT_INTR_ETP = SAV_STATUS.IDNT_INTR_ETP
WHERE TSAV.NUMR_EXTR_TCK_CLT = 'abcdefg'
--TSAV.DAT_HEUR_OUVR_TCK_CLT >= to_date('23/11/2015','dd/mm/yyyy')
AND TSAV.IDNT_ACTV_RES = '1'
AND OFFRE.LIBL_OFFR NOT LIKE '%TT%') TT
WHERE TT.rnk = 1 )
WITH DATA NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS ;

-- Concatenating the rows

WITH RECURSIVE SAV_TAB (SERV, SRV, OFR, DAT_ACTIVATION, LIBL_OFFR, IDNT_TCK_CLT, NUMR_TCK_CLT, DAT_OUVR_TCK_CLT, DAT_CLT_ACTION, LIBL_ETP, SEQQ)
AS
(SELECT
TOTO.IDNT_COMP_SERV,
TOTO.IDNT_EXTR_SRV,
TOTO.IDNT_EXTR_OFR,
TOTO.DAT_HEUR_ACTV,
TOTO.LIBL_OFFR,
TOTO.IDNT_INTR_TCK_CLT,
TOTO.NUMR_EXTR_TCK_CLT,
TOTO.DAT_HEUR_OUVR_TCK_CLT,
TOTO.DAT_HEUR_CLT_ACT,
TOTO.LIBL_ETP,
TOTO.SEQQ
FROM TOTO
WHERE TOTO.SEQQ = 1
UNION ALL
SELECT
TOTO.IDNT_COMP_SERV,
TOTO.IDNT_EXTR_SRV,
TOTO.IDNT_EXTR_OFR,
TOTO.DAT_HEUR_ACTV,
TOTO.LIBL_OFFR,
TOTO.IDNT_INTR_TCK_CLT,
TOTO.NUMR_EXTR_TCK_CLT,
TOTO.DAT_HEUR_OUVR_TCK_CLT,
TOTO.DAT_HEUR_CLT_ACT,
CAST(SAV_TAB.LIBL_ETP||' '||TOTO.LIBL_ETP as VARCHAR(10000)),
TOTO.SEQQ
FROM TOTO, SAV_TAB
WHERE TOTO.SEQQ = SAV_TAB.SEQQ + 1
AND SAV_TAB.NUMR_TCK_CLT = TOTO.NUMR_EXTR_TCK_CLT )
SELECT * from SAV_TAB ;
2 REPLIES
Junior Contributor

Re: "WITH RECURSIVE" CONCATENATION RESULT TRUNCATED

You must apply the cast in the seed query (the 1st query of a set operation determines the resulting datatype):

WITH RECURSIVE SAV_TAB (SERV, SRV, OFR, DAT_ACTIVATION, LIBL_OFFR, IDNT_TCK_CLT, NUMR_TCK_CLT, DAT_OUVR_TCK_CLT, DAT_CLT_ACTION, LIBL_ETP, SEQQ) 
AS
(SELECT
TOTO.IDNT_COMP_SERV,
TOTO.IDNT_EXTR_SRV,
TOTO.IDNT_EXTR_OFR,
TOTO.DAT_HEUR_ACTV,
TOTO.LIBL_OFFR,
TOTO.IDNT_INTR_TCK_CLT,
TOTO.NUMR_EXTR_TCK_CLT,
TOTO.DAT_HEUR_OUVR_TCK_CLT,
TOTO.DAT_HEUR_CLT_ACT,
CAST(TOTO.LIBL_ETP as VARCHAR(10000)),
TOTO.SEQQ
FROM TOTO
WHERE TOTO.SEQQ = 1
UNION ALL
SELECT
TOTO.IDNT_COMP_SERV,
TOTO.IDNT_EXTR_SRV,
TOTO.IDNT_EXTR_OFR,
TOTO.DAT_HEUR_ACTV,
TOTO.LIBL_OFFR,
TOTO.IDNT_INTR_TCK_CLT,
TOTO.NUMR_EXTR_TCK_CLT,
TOTO.DAT_HEUR_OUVR_TCK_CLT,
TOTO.DAT_HEUR_CLT_ACT,
SAV_TAB.LIBL_ETP||' '||TOTO.LIBL_ETP,
TOTO.SEQQ
FROM TOTO, SAV_TAB
WHERE TOTO.SEQQ = SAV_TAB.SEQQ + 1
AND SAV_TAB.NUMR_TCK_CLT = TOTO.NUMR_EXTR_TCK_CLT )
SELECT * from SAV_TAB ;
Enthusiast

Re: "WITH RECURSIVE" CONCATENATION RESULT TRUNCATED

It works with Cast in the seed query.

Thank you for your time.