Re: how can i find the result?

Hi Doug,

I think the wrinkle is, as you said with the NOT IN...it's like !ALL so as soon as you hit a null the query returns no rows...we could test that by changing not in to 'in' and you should get the rows that DO match.....if you can reconstruct the query without the 'not' you should be home and dry ....

Steve

Doug O'Leary wrote:

> Hey;
>
> I know I'm on the right track - just can't get all the way there &
> unfortunately ran out of time. Perhaps someone a little smarter than I
> am can take you the rest of the way...
>
> To find all the numbers that are currently in your list (convoluted
> method, but step 1):
>
> SQL> list
> 1 select no
> 2 from find_number
> 3 where no in
> 4 ( select rownum rnum
> 5 from all_objects
> 6 where rownum <= (
> 7 select max(no)
> 8 from find_number)
> 9* )
> SQL> /
>
> NO
> ----------
> 1
> 2
> 3
> 5
> 7
>
> Logically, setting 'not in' should find the numbers that *aren't* in your
> list; however, when I do that, I get a 'no rows selected'
>
> SQL> list
> 1 select no
> 2 from find_number
> 3 where no not in
> 4 ( select rownum rnum
> 5 from all_objects
> 6 where rownum <= (
> 7 select max(no)
> 8 from find_number)
> 9* )
> SQL> /
>
> no rows selected
>
> Hopefully, that'll point you in the right direction...
>
> Doug
>
> --
> -------------------
> Douglas K. O'Leary
> Senior System Administrator
> dkoleary_at_mediaone.net
Received on Fri Jun 15 2001 - 09:56:31 CDT