SVC_CATEGORIES_CF_V

Details

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