table has two rows
select id,value from IIO_SYSTEM_PARAMETER;
200 1027/1029/1000
199 1027/1029/1000
when I ran the below query, it is giving 7 records, instead of 3 records
query1: SELECT
REGEXP_SUBSTR(VALUE, '[^/]+', 1, LEVEL) DEST
FROM
IIO_SYSTEM_PARAMETER
WHERE id =200
CONNECT BY
LEVEL <= LENGTH(VALUE) - LENGTH(REPLACE(VALUE, '/')) + 1
;so, I created temp table with required row only
create table temp as
select * from IIO_SYSTEM_PARAMETER
WHERE id =200; and ran the string to rows query on temp table
query 2:SELECT
REGEXP_SUBSTR(VALUE, '[^/]+', 1, LEVEL) DEST
FROM
temp
CONNECT BY
LEVEL <= LENGTH(VALUE) - LENGTH(REPLACE(VALUE, '/')) + 1;and gave 3 records as result(which is correct)
why query 1 is giving 7 records, but query 2 is giving 3 records .
complete code:
DROP TABLE IIO_SYSTEM_PARAMETER CASCADE CONSTRAINTS;
CREATE TABLE IIO_SYSTEM_PARAMETER
(
ID INTEGER NOT NULL,
MODULE VARCHAR2(50 CHAR) NOT NULL,
NAME VARCHAR2(50 CHAR) NOT NULL,
DESCR VARCHAR2(200 CHAR),
VALUE VARCHAR2(2000 CHAR) DEFAULT ' ',
COMMENTS VARCHAR2(2000 CHAR)
);
Insert into IIO_SYSTEM_PARAMETER
(ID, MODULE, NAME, DESCR, VALUE)
Values
(200, 'DF_USER_INPUT', 'U_GOLIVE_LOC_OrderOpt', 'List of Locations going live in Order Opt', '1027/1029/1000');
Insert into IIO_SYSTEM_PARAMETER
(ID, MODULE, NAME, DESCR, VALUE)
Values
(199, 'DF_USER_INPUT', 'U_GOLIVE_LOC', 'List of Locations going live', '1027/1029/1000');
commit;
select id,value from IIO_SYSTEM_PARAMETER;
drop table temp;
create table temp as
select * from IIO_SYSTEM_PARAMETER
WHERE id =200;
select count(*) from (
SELECT
REGEXP_SUBSTR(VALUE, '[^/]+', 1, LEVEL) DEST
FROM
temp
CONNECT BY
LEVEL <= LENGTH(VALUE) - LENGTH(REPLACE(VALUE, '/')) + 1);--3records
SELECT
REGEXP_SUBSTR(VALUE, '[^/]+', 1, LEVEL) DEST
FROM
IIO_SYSTEM_PARAMETER
WHERE id =200
CONNECT BY
LEVEL <= LENGTH(VALUE) - LENGTH(REPLACE(VALUE, '/')) + 1 ;--7 records
The CONNECT BY LEVEL trick applies to every row in the input table. The WHERE clause filters the result of this hierarchy. So this links rows with ID 199 and 200, then hides those for ID 199.
Removing the WHERE clause and adding LEVEL and columns from the resource table may make this clearer:
select regexp_substr(value, '[^/]+', 1, level) dest, level, p.id
from iio_system_parameter p
connect by nocycle level <= length(value) - length(replace(value, '/')) + 1;
DEST LEVEL ID
1027 1 200
1029 2 200
1000 3 200
1000 3 199
1029 2 199
1000 3 200
1000 3 199
1027 1 199
1029 2 200
1000 3 200
1000 3 199
1029 2 199
1000 3 200
1000 3 199
To overcome this, generate the rows in a LATERAL or CROSS APPLY subquery. This uses the values from each row of the source table to generate the output.
For example:
select regexp_substr(value, '[^/]+', 1, lvl) dest
from iio_system_parameter,
lateral (
select level lvl from dual
connect by level <= length(value) - length(replace(value, '/')) + 1
)
where id =200;
DEST LVL ID
1027 1 200
1029 2 200
1000 3 200