Recursive Query Issue

Database
Enthusiast

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 ?





4 REPLIES
Enthusiast

Re: Recursive Query Issue

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

Enthusiast

Re: Recursive Query Issue

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<>'';

Enthusiast

Re: Recursive Query Issue

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


or there is a blog you take help from


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

Enthusiast

Re: Recursive Query Issue

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