All Forums Database
S_GALILEO 8 posts Joined 12/15
11 Dec 2015
"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 ;

 

dnoeth 4628 posts Joined 11/04
11 Dec 2015

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 ;

 

Dieter

S_GALILEO 8 posts Joined 12/15
13 Dec 2015

It works with Cast in the seed query.
Thank you for your time.

You must sign in to leave a comment.