CODEWITHSUNDEEP
sqloracle-databaseconnect-by
Tufan Barış Yıldırım
Tufan Barış Yıldırım

Reputation: 3962

Search in Path which created by SYS_CONNECT_BY_PATH in Connect By Prior Query

I'm using Connect By query to show hierarchical groups on a group, on this page I have a Combobox for selecting parent groups. But I want to hide all rows which contain group that currently being edited.

This is my table,

GROUPS
-------------
GROUP_ID, GROUP_NAME, GROUP_TYPE, PARENT_ID

My First query was;

SELECT * FROM 
(SELECT  
    GROUP_ID,
    LPAD('-',LEVEL,'-')|| GROUP_NAME GROUP_NAME,
     SYS_CONNECT_BY_PATH(GROUP_NAME, '->')  GROUP_NAME_PATH 
FROM  GROUPS
    START WITH PARENT_ID IS NULL AND TYPE='G'
    CONNECT BY PRIOR GROUP_ID=PARENT_ID)

This result was on grid.

27  -Generel Manager    ->Generel Manager,
28  --Project Manager   ->Generel Manager->Project Manager,
24  ---System Administrator ->Generel Manager->Project Manager->System Administrator
25  ---Software Developer   ->Generel Manager->Project Manager->Software Developer
26  ----Intern          ->Generel Manager->Project Manager->Software Developer->Intern

For example;

On page Editgroup.aspx?group_id=24 It shows all of these rows, but I want to hide all row paths which contains GROUP_ID = 24 because Group can not be selfchild.

I found a temporary solution with this query;

SELECT * FROM 
(SELECT  
    GROUP_ID,
    LPAD('-',LEVEL,'-')|| GROUP_NAME GROUP_NAME,
     SYS_CONNECT_BY_PATH(GROUP_NAME, '->') || ',' GROUP_NAME_PATH  
FROM  GROUPS
    START WITH PARENT_ID IS NULL AND GROUP_TYPE='G'
    CONNECT BY PRIOR GROUP_ID=PARENT_ID)
     WHERE  HIYERARSI_ID NOT LIKE '%,24,%'

24 is coming from url.

I think it won't be an efficient solution in the future when we got big data.

How can I detect in a smarter whay the path contains this id or not?

Upvotes: 1

Views: 3833

Answers (1)

arturro
arturro

Reputation: 1606

Below code will remove group number 24 and all its children:

SELECT   
GROUP_ID, 
LPAD('-',LEVEL,'-')|| GROUP_NAME GROUP_NAME, 
 SYS_CONNECT_BY_PATH(GROUP_NAME, '->')  GROUP_NAME_PATH  
FROM  GROUPS 
START WITH PARENT_ID IS NULL AND TYPE='G' 
AND GROUP_ID <> 24 /* added condition */
CONNECT BY PRIOR GROUP_ID=PARENT_ID 
AND GROUP_ID <> 24 /* added condition */

Upvotes: 2

Related Questions