All Forums Database
Niesh20us 78 posts Joined 06/13
01 May 2014
Recursive Query Issue

Recursive Query Issue

Hi Experts ,
 
I need your help in writing recusrion query . I have below table 
 
Number      FF_String        STRGY_TC
135            DD_B_SEG  48647_002
127            ATM_B_NP  48557_001|48557_002|48557_003|48558_004|48558_005|4855 8_006|48558_007|48558_008
 
 
And i have to generate below output 
 
Expected Output :-
 
Number       FF_String    Strgy    Test
135             DD_B_SEG  48647    002
127            ATM_B_NP   48557  001
127            ATM_B_NP   48557  002
127           ATM_B_NP    48557  003
127           ATM_B_NP    48558  004
127          ATM_B_NP    48558  005
127          ATM_B_NP    48558  006
127         ATM_B_NP     48558  007
127          ATM_B_NP    48558  008
 Can you help me please ?

 

 

Qaisar Kiani 337 posts Joined 11/05
01 May 2014

Did you try writing a SQL? Please share and let us know where you are stuck and someone can guide you in right direction.

Niesh20us 78 posts Joined 06/13
01 May 2014

Actually below sql is working for one row only but when i add below row it does not work , because there is no '|' . In the below code NONPROMO_POP is the table which contains those two rows.
 
Number      FF_String        STRGY_TC
135            DD_B_SEG  48647_002
 

WITH RECURSIVE PARSE_LIST (

Number,

FF_String  ,

STRGY_TC,REMAINDER,DPOS) AS

(

SELECT 

Number,

FF_String        ,

SUBSTRING( STRGY_TC FROM 1 FOR POSITION('|' IN STRGY_TC)-1),

SUBSTRING(STRGY_TC FROM POSITION('|' IN STRGY_TC)+1 FOR CHAR_LENGTH(STRGY_TC)) as REMAINDER,

CASE WHEN POSITION('|' IN (SUBSTRING(STRGY_TC FROM POSITION('|' IN STRGY_TC)+1 FOR CHAR_LENGTH(STRGY_TC)))) >0

THEN POSITION('|' IN (SUBSTRING(STRGY_TC FROM POSITION('|' IN STRGY_TC)+1 FOR CHAR_LENGTH(STRGY_TC)))) 

ELSE CHARACTER_LENGTH(REMAINDER)+1

END DPOS

FROM NONPROMO_POP

 

UNION ALL

 

SEL  

Number,

FF_String        ,

TRIM (BOTH ' ' FROM TRIM(BOTH '|' FROM SUBSTR(REMAINDER, 1, DPOS-1)))

,TRIM( BOTH ' ' FROM TRIM(BOTH '|' FROM SUBSTR(REMAINDER, DPOS+1)))

,CASE WHEN POSITION('|' IN (TRIM( BOTH ' ' FROM TRIM(BOTH '|' FROM SUBSTR(REMAINDER, DPOS+1))))) > 0      THEN POSITION('|' IN (TRIM( BOTH ' ' FROM TRIM(BOTH '|' FROM SUBSTR(REMAINDER, DPOS+1)))))

                ELSE CHARACTER_LENGTH(REMAINDER)

               END DPOS

FROM   PARSE_LIST

WHERE  DPOS > 0

)

SEL 

Number,FF_String,STRGY_TC

FROM PARSE_LIST 

WHERE STRGY_TC<>'';

Qaisar Kiani 337 posts Joined 11/05
02 May 2014

See my other post, I have provided the solution for a similar problem.

 

http://forums.teradata.com/forum/database/help-in-recursive-query-required

 

 

or there is a blog you take help from

http://walkingoncoals.blogspot.com/2009/12/fun-with-recursive-sql-part-2.html

 

Drop a note if you still facing issues while tweaking it according to your requirements.

Qaisar Kiani 337 posts Joined 11/05
02 May 2014

I have written something very quickly but haven't tested it. Probably some minor tweaks would do the trick.

CREATE SET TABLE foo (
Number_id integer,
FF_String VARCHAR(100),
STRGY_TC VARCHAR(100),
)
PRIMARY INDEX ( Number_id );

INSERT INTO FOO VALUES (135, 'DD_B_SEG', '48647_002'); 
INSERT INTO FOO VALUES (127, 'ATM_B_NP', '48557_001|48557_002|48557_003|48558_004|48558_005|485 5 8_006|48558_007|48558_008');

With Recursive LOOKUP (Number_id, FF_String, STRGY_TC ) As
(
Select    
Number_id
, FF_String
, substr(STRGY_TC, 1, index(STRGY_TC,'|')-1) || ' ' || substr(STRGY_TC, index(STRGY_TC,'|') +1, character_length(STRGY_TC)) as STRGY_TC
From	foo root
union all
Select 
direct.Number_id
, direct.FF_String
, substr(direct.STRGY_TC, index(direct.STRGY_TC,'|')+1,100 ) as STRGY_TC
From     LOOKUP direct
where index(direct.STRGY_TC,'|') >0)
select 	b.Number_id
		, b.FF_String
		, TRIM(substr(b.STRGY_TC, 1, index(b.STRGY_TC, '_')-1)) AS Strgy
		, TRIM(substr(b.STRGY_TC, index(b.STRGY_TC, '_')+1,character_length(b.STRGY_TC))) AS Test
from LOOKUP b
where index(b.STRGY_TC,'|')=0

 

You must sign in to leave a comment.