Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I retrieve free values?
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
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