For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
SELECT * FROM t m WHERE EXISTS ( SELECT NULL FROM t WHERE product = m.product AND ( min_val > m.max_val OR max_val < m.min_val ) );
P SUBCODE MIN_VAL MAX_VAL - ---------- ---------- ---------- A 0 0 99 A 1 0 99 A 4 0 99 A 5 45 60 A 6 20 40 A 7 0 99 A 8 60 65 A 9 0 99
10 start with -- This, start with, seems "clumpsy" 11 min_val in ( select min_val 12 from t 13 group by product, subcode, min_val)
14 connect by nocycle -- This, nocycle, really bothers me! 15 min_val >= prior min_val 16 and max_val <= prior max_val 17 and product = prior product 18 and subcode <> prior subcode
9 where connect_by_isleaf = 1 -- Why does this help me?
SQL> SELECT m.*, 2 CASE 3 WHEN 4 EXISTS ( 5 SELECT NULL 6 FROM t 7 WHERE product = m.product 8 AND ( min_val >= m.max_val 9 OR max_val <= m.min_val 10 ) 11 ) 12 THEN 1 END group_flag 13 FROM t m; PRODUCT SUBCODE MIN_VAL MAX_VAL GROUP_FLAG ------- ---------- ---------- ---------- ---------- A 0 0 99 A 1 0 99 A 2 18 67 A 3 20 65 A 4 0 99 A 5 45 60 1 A 6 20 40 1 A 7 0 99 A 8 60 65 1 A 9 0 99 10 rows selected. SQL>
15 min_val >= prior min_val
15 min_val > prior min_val
SQL> select distinct -- This, distinct, bothers me! 2 product 3 ,subcode 4 ,min_val 5 ,max_val 6 from t 7 where connect_by_isleaf = 1 -- Why does this help me? 8 connect by nocycle -- This, nocycle, really bothers me! 9 min_val > prior min_val 10 and max_val <= prior max_val 11 and product = prior product 12 and subcode <> prior subcode -- Not necessary 13 order by product 14 ,subcode 15 ,min_val 16 ,max_val; PRODUCT SUBCODE MIN_VAL MAX_VAL ------- ---------- ---------- ---------- A 5 45 60 A 6 20 40 A 8 60 65 3 rows selected. SQL>
select product, subcode, min_val, max_val from ( select * from t model partition by( product ) dimension by( subcode, min_val, max_val ) measures( 0 cross_points ) rules( cross_points[any,any,any] order by subcode = count(*)[any,min_val >= cv(max_val),any] + count(*)[any,any,max_val <= cv(min_val)] ) ) where cross_points > 0 order by min_val / P SUBCODE MIN_VAL MAX_VAL - ---------- ---------- ---------- A 6 20 40 A 5 45 60 A 8 60 65 SQL>
WHERE EXISTS ( SELECT NULL FROM t s WHERE s.product = m.product -- AND s.subcode != m.subcode AND ( s.min_val > m.max_val OR s.max_val < m.min_val ) );
... ( s.min_val > m.max_val OR s.max_val < m.min_val )
... s.subcode != m.subcode
insert into t (product, subcode, min_val, max_val) values ('B', 1, 0, 99); insert into t (product, subcode, min_val, max_val) values ('B', 2, 18, 67); insert into t (product, subcode, min_val, max_val) values ('B', 3, 60, 80); insert into t (product, subcode, min_val, max_val) values ('B', 4, 70, 99);
SQL> WITH t1 as (select * from t where subcode != 6) 2 SELECT * 3 FROM t1 m 4 WHERE EXISTS ( 5 SELECT NULL 6 FROM t1 7 WHERE product = m.product 8 AND ( min_val > m.max_val 9 OR max_val < m.min_val 10 ) 11 ) 12 / no rows selected SQL> WITH t1 as (select * from t where subcode != 6) 2 SELECT * 3 FROM t1 m 4 WHERE EXISTS ( 5 SELECT NULL 6 FROM t1 7 WHERE product = m.product 8 AND ( min_val >= m.max_val 9 OR max_val <= m.min_val 10 ) 11 ) 12 / P SUBCODE MIN_VAL MAX_VAL - ---------- ---------- ---------- A 5 45 60 A 8 60 65 SQL>
SQL> select * from t1 2 / P SUBCODE MIN_VAL MAX_VAL - ---------- ---------- ---------- A 0 0 99 A 1 0 99 A 2 18 67 A 3 20 65 A 4 0 99 A 5 45 60 A 6 20 40 A 7 0 99 A 8 50 65 A 9 0 99 10 rows selected. SQL>
constraint t_val_chk check (min_val <= max_val)
SQL>SELECT m.*, 2 CASE 3 WHEN 4 EXISTS ( 5 SELECT NULL 6 FROM t s 7 WHERE s.product = m.product 8 AND ( s.min_val >= m.max_val 9 OR s.max_val <= m.min_val 10 ) 11 ) 12 THEN 1 END group_flag 13 FROM t m 14 WHERE product = 'B'; PRODUCT SUBCODE MIN_VAL MAX_VAL GROUP_FLAG ------- ---------- ---------- ---------- ---------- B 1 0 99 B 2 18 67 1 B 3 60 80 B 4 70 99 1 SQL>
insert into t (product, subcode, min_val, max_val) values ('C', 1, 18, 60); insert into t (product, subcode, min_val, max_val) values ('C', 2, 18, 60); insert into t (product, subcode, min_val, max_val) values ('C', 7, 60, 65); insert into t (product, subcode, min_val, max_val) values ('C', 8, 60, 65);
SQL>SELECT m.*, 2 CASE 3 WHEN 4 EXISTS ( 5 SELECT NULL 6 FROM t s 7 WHERE s.product = m.product 8 AND ( s.min_val >= m.max_val 9 OR s.max_val <= m.min_val 10 ) 11 ) 12 THEN 1 END group_flag 13 FROM t m 14 WHERE product = 'C'; PRODUCT SUBCODE MIN_VAL MAX_VAL GROUP_FLAG ------- ---------- ---------- ---------- ---------- C 1 18 60 1 C 2 18 60 1 C 7 60 65 1 C 8 60 65 1 SQL>
SQL>SELECT m.*, 2 CASE 3 WHEN 4 EXISTS ( 5 SELECT NULL 6 FROM t s 7 WHERE s.product = m.product 8 AND ( s.min_val >= m.max_val 9 OR s.max_val <= m.min_val 10 ) 11 ) 12 THEN 1 END group_flag 13 FROM t m 14 WHERE product = 'A'; PRODUCT SUBCODE MIN_VAL MAX_VAL GROUP_FLAG ------- ---------- ---------- ---------- ---------- A 0 0 99 A 1 0 99 A 2 18 67 A 3 20 65 A 4 0 99 A 5 45 60 1 A 6 20 40 1 A 7 0 99 A 8 50 65 1 A 9 0 99 10 rows selected. SQL>