Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I retrieve free values?

Re: How do I retrieve free values?

From: Mark D Powell <mark.powell_at_eds.com>
Date: 22 Aug 2002 17:21:11 -0700
Message-ID: <178d2795.0208221621.2140c663@posting.google.com>


MSherrill_at_compuserve.com wrote in message news:<jmq9mu0qosjp6v7cgiu00nma2mt9du9966_at_4ax.com>...
> On Thu, 22 Aug 2002 10:18:01 +0200, "Lars Reineke"
> <reineke_at_kreiskrankenhaus-hameln.de> wrote:
>
> >Given a table A with a column NUMBER, which stores numerical values, let's
> >say in a range from 1 to 9999, how do I retrieve the values that are in that
> >range but not stored into that table?
>
> How do you retrieve values that don't exist? In the general case, you
> can't. Because they don't exist.
>
> But you can take advantage of the fact that you're looking at
> integers, and derive a set based on the existing set of integers.
> Very roughly . . .
>
> SELECT T1.[Number] + 1 AS GapStart
> FROM SomeTable as T1
> WHERE T1.[Number] + 1 NOT IN
> (SELECT T2.[Number] FROM SomeTable AS T2)
>
> This identifies the *start* of a gap in the sequence--the first value
> in a sequence of missing values. It ignores boundary issues.
>
> The simplest way to identify *every* missing value is to do a
> frustrated outer join between your table and a table of valid values.

Here is a piece of pl/sql code that will find the first missing number. You would need to add the cursor you want to use and in this sample there were minimum and maximum values passed in that limited the response range if desired that I ommited. -- initial counter then

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    ;                                      
end get_first_avail_qid ;

HTH -- Mark D Powell -- Received on Thu Aug 22 2002 - 19:21:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US