Help! WITH RECURSIVE does not return desired result..

Database

Help! WITH RECURSIVE does not return desired result..


Untitled Page


I have a data set in oracle (img1 in attachment)

ww_id fmly_nm given_nm jnj_supvr_ww_id
000000034654 CLARK LISA 000000033121
000000033121 ULLMANN MICHAEL 000000086902
000000086902 GORSKY ALEX 000000086902

below query in oracle

select a.wwid, LEVEL hier_lvl_no, SYS_CONNECT_BY_PATH(UPPER(a.wwidname)
, ' -> ') rptg_hier from (select to_number(a.ww_id) wwid, a.jnj_supvr_wwid_nbr,
to_number(a.ww_id)||' - '||a.fmly_nm||', '||a.given_nm wwidname from jjcomp.jjeds_emp
a ) a start with a.wwid = (86902) connect by nocycle prior a.wwid = to_number(a.jnj_supvr_wwid_nbr)

gives me following result

wwid hier_lvl_no rptg_hier
86902 1 -> 86902 - GORSKY, ALEX
33121 2 -> 86902 - GORSKY, ALEX -> 33121 - ULLMANN, MICHAEL
34654 3 -> 86902 - GORSKY, ALEX -> 33121 - ULLMANN, MICHAEL -> 34654 - CLARK, LISA

how i can generate the same result in TeraData
14.10 ?? i was trying some code using WITH RECURSIVE, however not able to see desired
result..

WITH RECURSIVE recursive_list(wwid,jnj_supvr_wwid_nbr, name, LVL) AS (
SELECT to_number(ww_id) wwid, jnj_supvr_wwid_nbr, MIN( to_number(ww_id) ||' - '||
fmly_nm || ', ' || given_nm) (VARCHAR(1000)), 1 FROM hrops_v.jjeds_emp where to_number(ww_id)
in ( 86902,33121,34654) GROUP BY 1 UNION ALL SELECT to_number(ww_id) wwid, jnj_supvr_wwid_nbr,
to_number(ww_id) ||' - '|| fmly_nm ||', ' || given_nm || ',' || name, LVL+1 FROM
hrops_v.jjeds_emp INNER JOIN recursive_list ON wwid = jnj_supvr_wwid_nbr AND to_number(ww_id)
||' - '|| fmly_nm || ', ' || given_nm > name ) SELECT wwid, LVL, name FROM recursive_list
QUALIFY RANK() OVER(PARTITION BY wwid,jnj_supvr_wwid_nbr ORDER BY LVL) = 1 where
wwid in ( 86902,33121,34654)

can any body please help me to produce
desired result shown above from oracle ??

4 REPLIES
Enthusiast

Re: Help! WITH RECURSIVE does not return desired result..

HI Ashish,

please check the below SQL , it will give you the desired output.

WITH RECURSIVE  RECURSIVE_TEST_1

(

WID,

HIER_LVL_NO,

RPTG_HIER,

JNJ_SUPVR_ID)

AS

(

SEL 

WW_ID,

1 ,

WW_ID||'-'||FMLY_NM||','||GIVEN_NM (VARCHAR(1000)),

JNJ_SUPVR_WW_ID

FROM RECURSIVE_TEST

WHERE WW_ID  =86902

UNION ALL

SEL

B.WW_ID,

HIER_LVL_NO+1,

RPTG_HIER ||'->'||TRIM(WW_ID||'-'||FMLY_NM||','||GIVEN_NM),

JNJ_SUPVR_WW_ID

FROM  RECURSIVE_TEST_1  A

INNER JOIN RECURSIVE_TEST  B

ON TRIM(A.WID)=TRIM(B.JNJ_SUPVR_WW_ID) 

WHERE  HIER_LVL_NO<4)

SEL WID,

HIER_LVL_NO,

RPTG_HIER

FROM RECURSIVE_TEST_1

QUALIFY ROW_NUMBER () OVER ( PARTITION BY  WID ORDER BY RPTG_HIER  ) =1;

please check and let me know if you have any issue.

Re: Help! WITH RECURSIVE does not return desired result..

Thanks Arun, That worked. now have different issue...

I have created recursive view



Replace RECURSIVE VIEW hrops_v.RECURSIVE_TEST_1 (WID,HIER_LVL_NO,RPTG_HIER,JNJ_SUPVR_ID) AS ( SEL WW_ID,1 , ' -> ' || TRIM(to_number(WW_ID) || ' - '||FMLY_NM||', '||GIVEN_NM (VARCHAR(1000))),JNJ_SUPVR_WW_ID FROM hrops_v.jjeds_emp WHERE WW_ID =86902 UNION ALL SEL B.WW_ID,HIER_LVL_NO+1,RPTG_HIER ||' -> '||TRIM(to_number(WW_ID) ||' - '||FMLY_NM||', '||GIVEN_NM),JNJ_SUPVR_WW_ID FROM RECURSIVE_TEST_1 A INNER JOIN hrops_v.jjeds_emp B ON TRIM(A.WID)=TRIM(B.JNJ_SUPVR_WW_ID) WHERE HIER_LVL_NO<4);



i am looking extra values (fields) so i was trying to create view from recursive view



CREATE VIEW hrops_v.AS_JJEDS_RPTG_HIER_01 AS

SEL WID,HIER_LVL_NO,RPTG_HIER, TRIM(OTRANSLATE(OREPLACE(SUBSTR (rptg_hier || '->', INSTR (rptg_hier || '>', '>', 1, 1 ) + 1, INSTR (rptg_hier || '>', '>', 1, 2 ) - INSTR (rptg_hier || '>', '>', 1, 1 ) - 1 ), '-',' '),OTRANSLATE (rptg_hier, '0123456789', ' '), ' ' )) level_1, CASE WHEN HIER_LVL_NO >1 THEN TRIM(OTRANSLATE(OREPLACE(SUBSTR (rptg_hier || '->', INSTR (rptg_hier || '>', '>', 1, 2 ) + 1, INSTR (rptg_hier || '>', '>', 1, 3 ) - INSTR (rptg_hier || '>', '>', 1, 2 ) - 1 ), '-',' '),OTRANSLATE (rptg_hier, '0123456789', ' '), ' ' )) ELSE ' ' END level_2

FROM hrops_v.RECURSIVE_TEST_1 QUALIFY ROW_NUMBER () OVER ( PARTITION BY WID ORDER BY RPTG_HIER ) =1;




i undersood i can not create view from recursive view (is my understanding correct?)

in that case how to generate output as above. any direction?

Enthusiast

Re: Help! WITH RECURSIVE does not return desired result..

Hi Ashish,

YES!! As per my knowledge ,we can't create view on top of Recursive view.

so better to create a temp table from the recursive  view and then create a new view  on Top of the temp table . may be it will help you.

Re: Help! WITH RECURSIVE does not return desired result..

Thanks Arun. I will try your suggestion.