Hi All, I'm getting error while using connect by prior in y hierarchical query . I am using the below table :
PROCESS_CONFIG_ID -- varchar2
PROCESS_CONFIG_ID_ORI -- varchar2
PROCESS_CONFIG_ATTR_ID --NUMBER
PROCESS_ID --NUMBER
PARENT -- varchar2
PREDECESSOR -- varchar2
SUCCESSOR -- varchar2
BINDING_LEVEL -- NUMBER
insert into DIM_PROCESS_CONFIG
( PROCESS_CONFIG_ID, PROCESS_CONFIG_ID_ORI, PROCESS_CONFIG_ATTR_ID, PROCESS_ID, PARENT, PREDECESSOR, SUCCESSOR, BINDING_LEVEL)
select 'CAPM', 'CAPM', 1, 1, null, null, null,
1 from dual union all
select 'CAPM#STAGE', 'CAPM#STAGE', 2, 1, 'CAPM', null, 'CAPM#CORE',
2 from dual union all
select 'CAPM#CORE', 'CAPM#MART', 50, 1, 'CAPM', 'CAPM#STAGE', ' CAPM#MART',
2 from dual union all
select 'CAPM#MART', 'CAPM#MART', 100, 1, 'CAPM', 'CAPM#CORE', null,
2 from dual union all
select 'CAPM#STAGE#ODI_PKG_01', 'CAPM#STAGE#ODI_PKG_01', 3, 1, 'CAPM#STAGE', null, null,
3 from dual union all
select 'CAPM#STAGE#ODI_PKG_01#MAP_01', 'CAPM#STAGE#ODI_PKG_01#MAP_01', 4, 1, 'CAPM#STAGE#ODI_PKG_01', null, 'CAPM#STAGE#ODI_PKG_01#MAP_04', 4 from dual union all
select 'CAPM#STAGE#ODI_PKG_01#MAP_04', 'CAPM#STAGE#ODI_PKG_01#MAP_04', 5, 1, 'CAPM#STAGE#ODI_PKG_01', 'CAPM#STAGE#ODI_PKG_01#MAP_01', 'CAPM#STAGE#ODI_PKG_01#MAP_07', 4 from dual union all
select 'CAPM#STAGE#ODI_PKG_01#MAP_07', 'CAPM#STAGE#ODI_PKG_01#MAP_07', 6, 1, 'CAPM#STAGE#ODI_PKG_01', 'CAPM#STAGE#ODI_PKG_01#MAP_04', 'CAPM#STAGE#ODI_PKG_01#MAP_10', 4 from dual union all
select 'CAPM#STAGE#ODI_PKG_01#MAP_10', 'CAPM#STAGE#ODI_PKG_01#MAP_10', 7, 1, 'CAPM#STAGE#ODI_PKG_01', 'CAPM#STAGE#ODI_PKG_01#MAP_07', null,
4 from dual union all
select 'CAPM#STAGE#ODI_PKG_02', 'CAPM#STAGE#ODI_PKG_02', 8, 1, 'CAPM#STAGE', null, null,
3 from dual union all
select 'CAPM#STAGE#ODI_PKG_02#MAP_02', 'CAPM#STAGE#ODI_PKG_02#MAP_02', 9, 1, 'CAPM#STAGE#ODI_PKG_02', null, 'CAPM#STAGE#ODI_PKG_02#MAP_05',
4 from dual
;
commit;
Using above table I'm trying to execute the below query.
SELECT process_config_attr_id,parent,
CASE WHEN CONNECT_BY_ROOT process_config_attr_id=process_config_attr_id THEN 1 ELSE 0 END is_top_lvl,
CONNECT_BY_ISLEAF is_btm,
CASE WHEN CONNECT_BY_ISLEAF=0 AND CONNECT_BY_ROOT process_config_attr_id<>process_config_attr_id THEN 1 ELSE 0 END not_root_with_child
FROM dim_process_config
START WITH PROCESS_CONFIG_ATTR_ID is null
CONNECT BY PRIOR process_config_attr_id=parent;
But I'm getting below error:
##############
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
######################
Please guide me .
Thanks & Regards
Ram