SVC_CATEGORIES_CF_V

Details

• Schema: FUSION

• Object owner: FUSION

• Object type: VIEW

Columns

Name
categ PATH lev STRIPE_CD LVL0_CAT_ID LVL1_CAT_ID LVL2_CAT_ID LVL3_CAT_ID LVL4_CAT_ID LVL5_CAT_ID LVL6_CAT_ID LVL7_CAT_ID LVL8_CAT_ID LVL9_CAT_ID

Query

SQL_Statement

SELECT /*+ALL_ROWS*/ ti.categ, ti.path as path_, ti.lev, ti.stripe_cd, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 10),categ)) LVL0_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 9),categ)) LVL1_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 8),categ)) LVL2_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 7),categ)) LVL3_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 6),categ)) LVL4_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 5),categ)) LVL5_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 4),categ)) LVL6_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 3),categ)) LVL7_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 2),categ)) LVL8_CAT_ID, to_number(NVL(REGEXP_SUBSTR(ti.path, '[^\]+', 1, 1),categ)) LVL9_CAT_ID FROM ( SELECT category_id AS categ, stripe_cd, SYS_CONNECT_BY_PATH(category_id, '\') AS path, level as lev FROM svc_categories_b c WHERE enabled_flag = 'Y' AND DELETED_FLAG ='N' CONNECT BY PRIOR category_id =parent_category_id START WITH parent_category_id IS NULL )ti