Re:

[Michael_Schutte@xxxxxxxxxxxx]

I don't think it's going to work.

I think I was right the first time with the +1... however, you would need
to test individually for the value of 1.


Michael Schutte
Admin Professional



midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 08:43:45 AM:

> 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.
>
>
>
> --
> 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.