Find overlapping in below data in plsql

Question

code  Low   High
A      0      99
E      0      99
T      0      99
A    100     **199**
E    100     **199**
T    100     **199**
A    **199**     299
E    **199**     299
T    **199**     299
A    300     399
E    300     399
T    300     399

I want to check the overlapping such as 199. If its overlap then throw the exception in oracle.

Answer 1

You can do it with a single query and then handle it via the IF condition.

Query to identify if an exception should be thrown:

SELECT MIN(CASE WHEN LAG_HIGH BETWEEN LOW AND HIGH THEN 'EXCEPTION'
                ELSE 'NO EXCEPTION'
           END) AS RESULT
  FROM (SELECT T.*,
               LAG(HIGH) OVER(PARTITION BY CODE ORDER BY LOW) AS LAG_HIGH
          FROM YOUR_TABLE T);

If you want all the codes for which exception is raised, then you can use the following query:

SELECT DISTINCT CODES FROM
(SELECT CASE WHEN LAG_HIGH BETWEEN LOW AND HIGH THEN CODE
               END AS CODES
      FROM (SELECT T.*,
                   LAG(HIGH) OVER(PARTITION BY CODE ORDER BY LOW) AS LAG_HIGH
              FROM YOUR_TABLE T))
WHERE CODES IS NOT NULL;