Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.
Of course to get all the values of the gaps themselves we are (as we have seen) going to have to synthesize some rows. An alternative to ALL_OBJECTS here is to use a table function. This gives us an interesting choice - we can either synthesize all the rows up front or we can synthesize rows on a row-by-row basis via the TABLE () syntax as required. First up is the row-by-row approach - perhaps if there are only a few gaps this could be a more efficient approach.Code:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER (10); 2 / Type created. SQL> CREATE TABLE table_name ( 2 column_name NUMBER (10)); Table created. SQL> INSERT INTO table_name 2 SELECT column_value 3 FROM TABLE (number_table (2, 4, 5, 8, 10)); 5 rows created. SQL> COMMIT; Commit complete. SQL> VARIABLE min NUMBER; SQL> VARIABLE max NUMBER; SQL> EXEC :min := 1; :max := 10; PL/SQL procedure successfully completed. SQL> SELECT now + 1 || DECODE (now + 2, nxt, NULL, ' - ' || (nxt - 1)) gap 2 FROM (SELECT now, LEAD (now) OVER (ORDER BY now) nxt 3 FROM (SELECT column_name now 4 FROM table_name 5 UNION ALL 6 SELECT column_value 7 FROM TABLE (number_table (:min - 1, :max + 1)))) 8 WHERE now + 1 != nxt; GAP -------------------------------------------------------------------------- 1 3 6 - 7 9 4 rows selected. SQL>
Alternatively we can (as mentioned) synthesize all the rows up-front using the table function and anti-join, outer-join or MINUS as suggested by Dapi and Jurij.Code:SQL> CREATE OR REPLACE FUNCTION many ( 2 p_rows IN NUMBER) 3 RETURN number_table PIPELINED 4 IS 5 BEGIN 6 FOR l_row IN 1..p_rows LOOP 7 PIPE ROW (l_row); 8 END LOOP; 9 RETURN; 10 END; 11 / Function created. SQL> SELECT now + column_value now 2 FROM (SELECT now, LEAD (now) OVER (ORDER BY now) nxt 3 FROM (SELECT column_name now 4 FROM table_name 5 UNION ALL 6 SELECT column_value 7 FROM TABLE (number_table (:min - 1, :max + 1)))), 8 TABLE (many (nxt - now - 1)) 9 WHERE now + 1 != nxt; NOW ---------- 1 3 6 7 9 5 rows selected. SQL>
If follows that we can extend these two examples to give us all the values (and also know which is fake or real).Code:SQL> SELECT now 2 FROM (SELECT :MIN + column_value - 1 now 3 FROM TABLE (many (:max - :min + 1))) a, 4 table_name b 5 WHERE column_name IS NULL 6 AND column_name(+) = now; NOW ---------- 1 3 6 7 9 5 rows selected. SQL>
Code:SQL> SELECT DECODE (now, now - 1 + column_value, 'REAL', 'FAKE') src, 2 now - 1 + column_value now 3 FROM (SELECT now, LEAD (now) OVER (ORDER BY now) nxt 4 FROM (SELECT column_name now 5 FROM table_name 6 UNION ALL 7 SELECT column_value 8 FROM TABLE (number_table (:min - 1, :max + 1)))), 9 TABLE (many (NVL (nxt - now, 1))) 10 WHERE now - 1 + column_value NOT IN (:min - 1, :max + 1); SRC NOW ---- ---------- FAKE 1 REAL 2 FAKE 3 REAL 4 REAL 5 FAKE 6 FAKE 7 REAL 8 FAKE 9 REAL 10 10 rows selected. SQL> SELECT DECODE (column_name, NULL, 'FAKE', 'REAL') src, now 2 FROM (SELECT :MIN + column_value - 1 now 3 FROM TABLE (many (:max - :min + 1))) a, 4 table_name b 5 WHERE column_name(+) = now; SRC NOW ---- ---------- FAKE 1 REAL 2 FAKE 3 REAL 4 REAL 5 FAKE 6 FAKE 7 REAL 8 FAKE 9 REAL 10 10 rows selected. SQL>
Reply With Quote