Tree query and CASE query in Oracle

When a table has hierarchical data, you can use hierarchical query to display the data results more intuitively and the hierarchical relationship between the data. The following query example:

Select lpad ('', 3 * (t2.grl _ grouplevel-1) | t1.cif _ namecn from cb_cst_inf t1, cs_group_rel t2 where t1.cif _ cstno = t2.grl _ cstid
Start with t2.grl _ cstid = 'cb10001810' and t2.grl _ groupid = '000000'
Connect by prior t2.grl _ cstid = t2.grl _ uplevelid;
-- The grl_uplevelid field in the cs_group_rel table reflects the hierarchical relationship. The grl_uplevelid at the highest level is null.
-- Start with: used to specify the row following the hierarchical Query
-- Connect by: used to specify the relationship between the parent row and the Child row. In this condition, PRIOR must be used to reference the parent row. The syntax is as follows :... PRIOR expr = expr or... expr = PRIOR expr

 

CASE query:
Select lpad ('', 5 * (t2.grl _ grouplevel-1) | t1.cif _ namecn name,
Case when t2.grl _ grouplevel = '1' then''
When t2.grl _ grouplevel = '2' then' Level 1 branch'
Else 'end level_name from cb_cst_inf t1, cs_group_rel t2 where t1.cif _ cstno = t2.grl _ cstid -- and t1.cif _ cstno = 'cb10001810'
Start with t2.grl _ uplevelid is null and t2.grl _ groupid = '20140901'
Connect by prior t2.grl _ cstid = t2.grl _ uplevelid;