Re: how can i find the result?

From: Mark D Powell <mark.powell_at_eds.com>
Date: 15 Jun 2001 06:25:16 -0700
Message-ID: <178d2795.0106150525.48788dc0@posting.google.com>

"d????" <illudus2_at_kornet.net> wrote in message news:<9gckb3$1r6$1_at_imsinews.kornet.net>...
> Suppose, there is a table, abc, with columns, no(smallint) and
> name(varchar(10)).
> and theer are records as follows,
>
> no name
> == =====
> 1 dfjdfdfd
> 2 dfefee
> 3 wuwu
> 5 mmmm
> 7 yyyy
>
> In this situation, I would like to find out the missing numbers.
> So when I query, I want to get the result 4 and 6.
>
> If i use the below query,
>
> select rownum from abc where rownum <= 5
> MINUS
> select no from abc;
>
> the result is
> rownum
> =======
> 4
>
> But I want to get the result 4 and 6 as a missing no.
> What was I wrong? How can I solve this problem?
>
> Thanks in advance,

I would use pl/sql to solve a problem like this. The following is part of a function that uses a no_data_found exception to capture a missing value in a sequence. The logic is simple run a loop starting at a desired starting value until I get a maximum value that I want to check. The logic returns the next open number. In your case a select max(key) would probably surfice to find the ending value:

v_ctr should be set to our desired starting value loop

--                               if query id not found 'exception'

  select q_id
    into v_qid
    from dq3_query

   where  q_id  =  v_ctr         ;
--                               if query id found then increment
counter
--                               and check counter less than ending
value
  v_ctr  :=  v_ctr + 1           ;
  if  v_ctr  >  v_end_qid  then
      return  0                  ;
  end if                         ;
end loop                         ;
return  0                        ;
--                               at end - no openings in range
--                               when query id not found return open

id
exception
  when no_data_found then

        return v_ctr ; -- v_ctr is equal to non-existant q_id
end dba_dq3_get_first_avail_qid ;

With version 8.1.7 you might be able to use an analytic function to do this in pure SQL, but I haven't tried to figure that out yet so here is something I have actually used.

• Mark D Powell --

Received on Fri Jun 15 2001 - 08:25:16 CDT