Re: SQL Query Question

That's classic safety problem.

If database were allowed to have unsafe infinite pseudotable, then a query

select rownum from inf_pseudotable where rownum < 100;

would return all integers in your range. (BTW, I was asking that table from oracle folks:-) Otherwhise just take a table big enough instead:-(

In article <qNRK6.151$V5.828_at_news1.rivrw1.nsw.optushome.com.au>, Charcoal says...
>
>Ok thanks for the help Mladen and Arto. I was hoping there was a way to do
>it without creating another table... but if not then I guess I have no
>choice.
>
>
>
>"Arto Viitanen" <av_at_cs.uta.fi> wrote in message
>news:wed79gqaz4.fsf_at_siwenna.cs.uta.fi...
>> >>>>> "who" == who <Charcoal> writes:
>>
>> who> Hi people, Assume I have a table which is just a list of numbers
 (only
>> who> 1 column). I want to perform a query which shows every number under
 100
>> who> which is NOT in the table. How do I do this?
>>
>> who> For example, in my MyTable there is the following data: 1, 4, 7, 32,
 54
>> who> (there are 5 records in my table).
>>
>> who> Now how would I write a query to show all the numbers < 100 that are
>> who> not in this table? ie, it should show 2, 3, 5, 8, .... etc.
>>
>>
>> Create another table, that has numbers from 1 to 100. Then make a select
 like
>>
>> SELECT column
>> FROM table
>>
>> DIFFERENCE
>>
>> SELECT column
>> FROM onetohundred
>>
>> (or using NOT IN, if you prefer).
>>
>> That is, SQL and relational model knows nothing about number theory.
>>
>>
>>
>> --
>> Arto V. Viitanen av_at_cs.uta.fi
>> University of Tampere, Department of Computer and Information Sciences
>> Tampere, Finland http://www.cs.uta.fi/~av/
>
>
Received on Fri May 11 2001 - 18:06:44 CEST