Home » SQL & PL/SQL » SQL & PL/SQL » missing values !!!
icon4.gif  missing values !!! [message #309968] Sun, 30 March 2008 15:49 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

if i have column with sequence values , but there are some values missing ...... like that :-

create table v ( id number );

insert into v values (1);
insert into v values (2);
insert into v values (4);
insert into v values (5);
insert into v values (6);
insert into v values (8);
insert into v values (9);
insert into v values (10);
insert into v values (11);
insert into v values (12);

SQL> select * from v;

        ID
----------
         1
         2
         4
         5
         6
         8
         9
        10
        11
        12

10 rows selected.

SQL> commit;

Commit complete.


of course I know the primary key and constraints and all that ,and I'm sure you understand that I wrote a little example to simplified the idea.

what I need here that is there any way to find out the missing values ( 3 , 7 in my example ) by select query ?!
Re: missing values !!! [message #309972 is a reply to message #309968] Sun, 30 March 2008 16:10 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's a section in the OraFAQ Forum Guide which shows you how to get a quick answer to your questions. One of them is use of the Search facility this forum offers. I found the solution in a matter of a few seconds. How long will you need?
Re: missing values !!! [message #309973 is a reply to message #309968] Sun, 30 March 2008 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Quote:

One of them is use of the Search facility this forum offers


Expecting folks with 100+ posts to use the Search function places an unreasonable burden on their fragile psyche.
Why should they bother looking for answers when others gladly continue to spoon feed them.

The IGNORE feature eliminates urge feed the lazy.

Some posters would fail a Turing Test on a regular basis.
icon10.gif  OK [message #310435 is a reply to message #309968] Tue, 01 April 2008 04:47 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

well ............ thanks for your polite answers , like you were always Laughing

that's the answers that I get from other forums :-

SELECT ROWNUM missing_values
FROM dict WHERE ROWNUM <
(SELECT MAX (ID)FROM v)
MINUS
(SELECT ID FROM v )
/


and this Long Block working finely too :-

DECLARE
v_cursor INTEGER;
v_cur1 INTEGER;
v_val NUMBER;
v_ret INTEGER;
v_tab VARCHAR2(200) := '&Table_Name';
v_pkey VARCHAR2(100) := '&Primary_key';
v_where VARCHAR2(5000) := nvl('&Where_Clause',' ');
v_next_num NUMBER := 1;
v_sql VARCHAR2(6000) := 'SELECT a.'||v_pkey||'+'||v_next_num||
' FROM '||v_tab||' a, '||v_tab||' b
WHERE a.'||v_pkey||'+'||v_next_num||' = b.'||v_pkey||' (+)
AND b.'||v_pkey||' IS NULL
AND a.'||v_pkey||' <> (SELECT max(z.'||v_pkey||') FROM '||v_tab||' z)
'||v_where||' ORDER BY '||'a.'||v_pkey;
v_new_sql VARCHAR2(8000);
v_value NUMBER;
v_ignore INTEGER;
v_new_value NUMBER;

BEGIN
        v_cursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_cursor,v_sql,DBMS_SQL.NATIVE);
        DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_value);
        DBMS_OUTPUT.PUT_LINE('              ');
        DBMS_OUTPUT.PUT_LINE('Missing Values');
        DBMS_OUTPUT.PUT_LINE('--------------');
        DBMS_OUTPUT.PUT_LINE('              ');

        v_ignore := DBMS_SQL.EXECUTE(v_cursor);
          LOOP
                v_ret := DBMS_SQL.FETCH_ROWS(v_cursor);
                EXIT WHEN v_ret = 0;
                DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_value);
                v_next_num:=1;
                v_new_value:=v_value-1;
                LOOP
                   v_next_num:=v_next_num+1;
                   v_new_sql:='SELECT a.'||v_pkey||'+'||v_next_num||'

FROM '||v_tab||' a, '||v_tab||' b
                                WHERE a.'||v_pkey||'+'||v_next_num||' =
b.'||v_pkey||' (+) AND b.'||v_pkey||' IS NULL
                                AND a.'||v_pkey||' <> (SELECT
max(z.'||v_pkey||') FROM '
                                ||v_tab||' z) AND a.'||v_pkey||' =
'||v_new_value;
                   v_cur1 := DBMS_SQL.OPEN_CURSOR;
                   DBMS_SQL.PARSE(v_cur1,v_new_sql,DBMS_SQL.NATIVE);
                   v_ignore:=DBMS_SQL.EXECUTE(v_cur1);
                   v_ret:=DBMS_SQL.FETCH_ROWS(v_cur1);

                   DBMS_SQL.CLOSE_CURSOR(v_cur1);
                   IF v_ret = 0 THEN
                      IF v_next_num = 2 THEN
                            DBMS_OUTPUT.PUT_LINE(to_char(v_value));
                            IF DBMS_SQL.IS_OPEN(v_cur1) THEN
                              DBMS_SQL.CLOSE_CURSOR(v_cur1);
                            END IF;
                            EXIT;
                      END IF;
        
DBMS_OUTPUT.PUT_LINE(to_char(v_value)||'-'||to_char(v_new_value+v_next_num-1));

                      IF DBMS_SQL.IS_OPEN(v_cur1) THEN
                      DBMS_SQL.CLOSE_CURSOR(v_cur1);
                      END IF;
                      EXIT;
                   END IF;
                END LOOP;
          END LOOP;
        DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/


OK ...... see ya Razz
Re: missing values !!! [message #310465 is a reply to message #309968] Tue, 01 April 2008 06:37 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO there is a problem with

SELECT ROWNUM missing_values
FROM dict WHERE ROWNUM <
(SELECT MAX (ID)FROM v)
MINUS
SELECT ID FROM v


What happens when the MAX(ID) exceeds the number of rows in DICT?

Try

select rownum missing_values from dual
connect by  level < (SELECT MAX (ID)FROM v)
MINUS
SELECT ID FROM v


HTH.
icon14.gif  OK , you right [message #310598 is a reply to message #310465] Tue, 01 April 2008 14:44 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

thanks michael_bialik ..... you right ..... thanks a lot.

if there another answers ..... be sure I will be so welcome Very Happy
Re: missing values !!! [message #324150 is a reply to message #310465] Sat, 31 May 2008 03:18 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
michael_bialik wrote on Tue, 01 April 2008 06:37
IMHO there is a problem with

SELECT ROWNUM missing_values
FROM dict WHERE ROWNUM <
(SELECT MAX (ID)FROM v)
MINUS
SELECT ID FROM v


What happens when the MAX(ID) exceeds the number of rows in DICT?

Try

select rownum missing_values from dual
connect by  level < (SELECT MAX (ID)FROM v)
MINUS
SELECT ID FROM v


HTH.


SQL> select rownum missing_values from dual
2 connect by level < (SELECT MAX (ID)FROM v)
3 MINUS
4 SELECT ID FROM v;
connect by level < (SELECT MAX (ID)FROM v)
*
ERROR at line 2:
ORA-01473: cannot have subqueries in CONNECT BY clause

Re: missing values !!! [message #324153 is a reply to message #324150] Sat, 31 May 2008 03:37 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
Previous Topic: DESC not able to display the column name
Next Topic: i need pl/sql code
Goto Forum:
  


Current Time: Mon Dec 05 09:12:18 CST 2016

Total time taken to generate the page: 0.05351 seconds