INV_LPN_HIERARCHIES_V

Details

• Schema: FUSION

• Object owner: INV

• Object type: VIEW

Columns

Name
LPN_ID PARENT_LPN_ID_1 PARENT_LPN_ID_2 PARENT_LPN_ID_3 PARENT_LPN_ID_4 PARENT_LPN_ID_5 PARENT_LPN_ID_6 PARENT_LPN_ID_7 PARENT_LPN_ID_8 PARENT_LPN_ID_9 PARENT_LPN_ID_10

Query

SQL_Statement

SELECT TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 1)) AS LPN_ID ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 2)) AS PARENT_LPN_ID_1 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 3)) AS PARENT_LPN_ID_2 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 4)) AS PARENT_LPN_ID_3 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 5)) AS PARENT_LPN_ID_4 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 6)) AS PARENT_LPN_ID_5 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 7)) AS PARENT_LPN_ID_6 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 8)) AS PARENT_LPN_ID_7 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 9)) AS PARENT_LPN_ID_8 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 10)) AS PARENT_LPN_ID_9 ,TO_NUMBER(REGEXP_SUBSTR(PATH, '[^/ ]+', 1, 11)) AS PARENT_LPN_ID_10 FROM (SELECT SYS_CONNECT_BY_PATH(LPN_ID, '/') PATH FROM INV_LICENSE_PLATE_NUMBERS WHERE CONNECT_BY_ISLEAF =1 CONNECT BY PRIOR PARENT_LPN_ID=LPN_ID )