Re:

[Michael_Schutte@xxxxxxxxxxxx]

Well, then let's turn the +1 to a -1 and add a new condition to the where
clause...

where not exists(same as before)
   and a.field-1 > 0


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.






                                                                           
             rob@xxxxxxxxx                                                 
             Sent by:                                                      
             midrange-l-bounce                                          To 
             s@xxxxxxxxxxxx            Midrange Systems Technical          
                                       Discussion                          
                                       <midrange-l@xxxxxxxxxxxx>           
             04/16/2008 08:41                                           cc 
             AM                                                            
                                                                   Subject 
                                       Re:                                 
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




This now gives me a 4
> Select  Min(thekey+1) FROM qtemp.foxwell a
   WHERE not exists (select '1' from qtemp.foxwell b
  where a.thekey+1 = b.thekey)

When the file contains 2, 3 and 5.

I think he would want a 1 first.  And I am not sure if he wanted all
within the 1-10 range.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Michael_Schutte@xxxxxxxxxxxx
Sent by: midrange-l-bounces@xxxxxxxxxxxx
04/16/2008 08:36 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re:






But you're probably right.  I didn't give a good solution.  It quick
without testing.

Exec sql
Select  min(a.field+1) from file a
where not exists(select '1' from file b where a.field+1 = b.field)

that should work.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.



midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 08:31:05 AM:

> I didn't understand this.  So I tried
>
> Select  Min(thekey) FROM qtemp.foxwell a
>  WHERE not exists (select '1' from qtemp.foxwell b
> where a.thekey = b.thekey
>  and  a.thekey = b.thekey + 1)
>
> on this table
> CREATE TABLE QTEMP.FOXWELL (THEKEY INT NOT NULL WITH DEFAULT)
> with these values:  2, 3, 5
> and I got a 2 as a result.
>
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> Michael_Schutte@xxxxxxxxxxxx
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 04/16/2008 08:20 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> cc
>
> Subject
> Re:
>
>
>
>
>
>
> sorry I put the +1 on the wrong field.
>
>
> Michael Schutte
> Admin Professional
>
>
>
>
>
> midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 08:18:25 AM:
>
> > Exec SQL
> > Select  Min(Field) FROM FILE  a
> >  WHERE not exists (select '1' from file b where a.key1 = b.key1 and
> a.field
> > = b.field+1)
> >
> >
> > Michael Schutte
> > Admin Professional
> > Bob Evans Farms, Inc.
> >
> >
> >
> >
> >
> > midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 04:57:07 AM:
> >
> > > Hi all,
> > >
> > > Is there a way to select the numbers missing from a list of numbers
?
> > > I mean, if I have a table with keys  1, 2, 3, 5, 6, 9,
> > >
> > > can I find the numbers missing between 1 and 10? ie, return 4, 7, 8,
> 10.
> > > I could create a table with 1 zone filled with the numbers 1 to 10
> > > and then do an outer join. But I need this in a program. I need to
> > > find the smallest missing number in a given range of numbers.
> > >
> > > Thanks.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.