Query to find out rows deleted from a table

ORA_AJ

Hi

Suppose i have a table with a sequence i.e its an header table containing GRN nos.Now suppose someone has deleted some of serials from it like i am having 1,2,4,5,6 etc 3 is missing.Now what will be the query to find out what are the serials that are missed out in the table.


--Ajay Sharma

MaheshKaila

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

ORA_AJ

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

MaheshKaila

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

Karthick2003

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

Aketi Jyuuzou

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

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

MaheshKaila

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

ORA_AJ

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

JustinCave

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

788759

Mahesh Kaila wrote:

Try this one



<PRE class=jive-pre><CODE class="jive-code jive-java">WITH test AS (SELECT <FONT color=navy>'1'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'2'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'4'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'5'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'6'</FONT> id

FROM DUAL)

SELECT TO_CHAR (l)

FROM (SELECT LEVEL l

FROM DUAL

CONNECT BY LEVEL &lt;= (SELECT MAX (id)

FROM test))

MINUS

SELECT id

FROM test

</CODE></PRE>



Regards,

Mahesh Kaila

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>!