WITH RECURSIVE

Analytics
Enthusiast

WITH RECURSIVE

create tabel ---
CREATE SET TABLE RAHUL_DB.cbn_cls ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
cbn_cls_id INTEGER,
cbn_cls VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( cbn_cls_id );

insert into rahul_db.cbn_cls values(3,'J30Y175');
insert into rahul_db.cbn_cls values(4,'Y50');
insert into rahul_db.cbn_cls values(6,'J12Y102');
insert into rahul_db.cbn_cls values(7,'J30Y224');
insert into cbn_cls values(9,'Y121');
insert into cbn_cls values(10,'P11J55W33Y111');
insert into rahul_db.cbn_cls values(8,'J35Y205');

WITH RECURSIVE cbn_cls_tmp (cbn_cls_id,cbn_cls,cbn_ty,cbn_val,str_len) AS
(
SELECT cbn_cls_id,
cbn_cls,
Substr(cbn_cls,1,1) As cbn_ty1,
Substr(cbn_cls,2,1)||
Case
When Substr(cbn_cls,3,1)
NOT IN ('J','P','W','Y') Then Substr(cbn_cls,3,1)||
Case
When Substr(cbn_cls,4,1)
NOT IN ('J','P','W','Y') Then Substr(cbn_cls,4,1)
Else ''
END
Else ''
End As cbn_val ,
Character(cbn_val)+2 As str_len
From cbn_cls

UNION ALL

SELECT T.cbn_cls_id,
Substr(T.cbn_cls From T.str_len) As cbn_cls2,
Substr(cbn_cls2,1,1) As cbn_ty2,
Substr(cbn_cls2,2,1)||
Case
When Substr(cbn_cls2,3,1)
NOT IN ('J','P','W','Y') Then Substr(cbn_cls2,3,1)||
Case
When Substr(cbn_cls2,4,1)
NOT IN ('J','P','W','Y') Then Substr(cbn_cls2,4,1)
Else ''
END
Else ''
End As cbn_val2 ,
Character(cbn_val2)+2 As str_len2
From cbn_cls_tmp T,cbn_cls R
Where R.cbn_cls_id = T.cbn_cls_id
)

sel *
From cbn_cls_tmp;

i got an error at " Substr(T.cbn_cls From T.str_len) " this line when i try to extract
cbn_cls value from the T.str_length poastion
and also
unable to get the RECURSIVE query

the out put i requie like
--------------------------------
cbn_cls_id--cbn_cls------cbn_ty1----cbn_val--str_len
1----------P14J64Y265-------P--------4--------4
1----------J64Y265----------J--------64-------4
1----------Y265-------------Y--------26-------5
2----------Y36--------------Y--------36-------4
3----------J30Y175----------J--------30-------4
3----------Y175-------------Y--------175------5
3 REPLIES
Teradata Employee

Re: WITH RECURSIVE

The problem with SUBSTR is mixed syntax. You can either say
SUBSTR(T.cbn_cls,T.str_len) or SUBSTRING(T.cbn_cls FROM T.str_len).

As far as the recursion, there are two problems.
First is the negated condition (NOT IN), but you can rewrite that as an equivalent positive condition such as:
CASE WHEN SUBSTR(cbn_cls,3,1)
IN ('J','P','W','Y') THEN SUBSTR(cbn_cls,2,1)
ELSE CASE WHEN SUBSTR(cbn_cls,4,1)
IN ('J','P','W','Y') THEN SUBSTR(cbn_cls,2,2)
ELSE SUBSTR(cbn_cls,2,3)
END
END AS cbn_val ,

Second, you need a WHERE condition that will terminate the recursion, e.g.:
WHERE R.cbn_cls_id = T.cbn_cls_id
AND cbn_cls2 <> ''

Enthusiast

Re: WITH RECURSIVE

THANK YOU VERY MUCH ,
AND 1 MORE DOUBT
WHY YOU ARE TRYING TO GIVE "IN" INSTAD OF "NOT IN"
Teradata Employee

Re: WITH RECURSIVE

Because NOT IN is one of the elements not permitted within a recursive statement in a WITH RECURSIVE clause - see SQL Reference: Data Manipulation Statements; SELECT Statement, WITH RECURSIVE Clause, WITH and WITH RECURSIVE Restrictions. If you try to use NOT IN you'll get error 6917: Illegal or unsupported use of negation inside a recursive query/view.

I picked one way to avoid NOT IN. You could also use something like this:
Substr(cbn_cls,3,1) <> 'J' AND
Substr(cbn_cls,3,1) <> 'P' AND
Substr(cbn_cls,3,1) <> 'W' AND
Substr(cbn_cls,3,1) <> 'Y'