All Forums Analytics
24 Nov 2007
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 ALLSELECT 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 querythe out put i requie like --------------------------------cbn_cls_id--cbn_cls--- ---cbn_ty1----cbn_val--str_len1----------P14J64Y265--- ----P--------4--------41----------J64Y265----------J-- ------64-------4 1----------Y265-------------Y--------26-------52------ ----Y36--------------Y--------36-------43---------- J30Y175----------J--------30-------43----------Y175--- ----------Y--------175------5

Fred 1096 posts Joined 08/04
24 Nov 2007

The problem with SUBSTR is mixed syntax. You can either saySUBSTR(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)ENDEND AS cbn_val ,Second, you need a WHERE condition that will terminate the recursion, e.g.:WHERE R.cbn_cls_id = T.cbn_cls_idAND cbn_cls2 <> ''

26 Nov 2007

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

Fred 1096 posts Joined 08/04
26 Nov 2007

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' ANDSubstr(cbn_cls,3,1) <> 'P' ANDSubstr(cbn_cls,3,1) <> 'W' ANDSubstr(cbn_cls,3,1) <> 'Y'

You must sign in to leave a comment.