Oracle Database Discussions

Try this one

WITH test AS (SELECT '1' id
              FROM DUAL
              UNION ALL
              SELECT '2' id
              FROM DUAL
              UNION ALL
              SELECT '4' id
              FROM DUAL
              UNION ALL
              SELECT '5' id
              FROM DUAL
              UNION ALL
              SELECT '6' id
              FROM DUAL)
SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM test))
MINUS
SELECT id
FROM test 

Regards,
Mahesh Kaila

Dear Mahesh


In my case Number of records is not fixed i mean to say its not neccesary that data is there for 1-6 only.It can be upto 100..


--Ajay Sharma

SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM <YOUR_TABLE>))
MINUS
SELECT id
FROM <YOUR_TABLE>

- Mahesh Kaila

What if i delete 4,5 and 6 will your query find it?

I like recursive with clause B-)
Hehe I used PostgreSQL8.4 because I do not have Oracle11gR2 :D

WITH recursive t(Val) AS (
values(1),(2),(4),(5),(6),(12)),
rec(Val,LeadVal) as(
select Val+1,LeadVal
  from (select Val,Lead(Val) over(order by Val) as LeadVal
          from t) tmp
 where Val+1 < LeadVal
union all
select Val+1,LeadVal
  from rec
 where Val+1 < LeadVal)
select Val from rec;

 val
-----
   3
   7
   8
   9
  10
  11

WITH test AS (SELECT '1' id
              FROM DUAL
              UNION ALL
              SELECT '2' id
              FROM DUAL
              UNION ALL
              SELECT '4' id
              FROM DUAL
              UNION ALL
              SELECT '5' id
              FROM DUAL
              UNION ALL
              SELECT '6' id
              FROM DUAL)
SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      WHERE LEVEL >= (SELECT MIN (id)
                 FROM test)
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM test))
MINUS
SELECT id
FROM test

Hi mahesh

I am using Oracle 8.1.5.0 and whenever i uses your query containing Connect by level I gets an errror saying Ora-01436 : CONNECT BY loop in user data..

Any comments about this.

--Ajay Sharma

CONNECT BY LEVEL was not available in 8.1.5. In 8.1.5, the simplest solution would be to select a ROWNUM from a table that has more rows than the TEST table. Assuming that DBA_OBJECTS is sufficiently large

SELECT rownum id
  FROM dba_objects
MINUS
SELECT id
  FROM test

As an FYI, if you're using an ancient version of Oracle like 8.1.5, it's helpful to mention that up front. There have been a lot of advances in the 8 major releases since then.

Justin

Where can I find the related documentation? It has been solved, but why's that? Many thanks to your <A class=bodylinkwhite href="http://www.software-to-convert.com/myspace-video-conversion-software/myspace-video-to-iriver-lplayer-software.html"><FONT face=tahoma,verdana,sans-serif color=#000 size=1>explanation</FONT></A>!