Alastair,
Maybe this: (keeping in mind it's not tested, of course)
Add a column to your codes table to flag it's being in use (Flag integer)
Set error var ErrVar
Set v vTestEntry Text
Set v vCode Text
Set v vCodeFound Integer
Dec Cursor c1 for sel CodesInUse from dataTable
open c1
While 1 < 10 Then
-- get data entry
Fetch c1 into vTestEntry
If sqlcode = 100 or errVar <> 0 Then
break
endif
-- Cursor against only codes not flagged from previous passes
Declare Cursor c2 for select Code from CodeTable where Flag is null
open c2
-- check each unused code against the test data
While 1 < 10 Then
Fetch c2 into vCode
If sqlcode = 100 or errVar <> 0 Then
break
endif
Set v vCodeFound = (sloc(.TestEntry,.vCode))
If vCodeFound > 0 Then
update Codes set Flag = 1 where current of c2
endif
endwhile
endwhile
Ben Petersen
On 9 Sep 2003, at 6:46, Alastair Burr wrote:
> Thanks Bill, I will double check, but I need a NOT IN that looks at the
> content of the field to find each of the two-character IDs rather one that
> looks at the whole field.
>
> It's a bit like using an automatically repeating SSUB where the substring
> number increments by one automatically for each possible ID code, knows how many
> times to do it for each row and only reports the ones it doesn't find - not much
> to ask!
>
> Let me try and illustrate it quickly:
>
> ID codes in rows in lookup table:
> AA
> AB
> AC
> BA
> BB
> BC
>
> Data in rows in data column in data table:
> AA,AB
> AA,AB,AC
> AA,AB,AC,AB
> AA,BC
> BA,BC
>
> I want to find in the lookup table the row BB. It's easy enough to create a temp
> table from the data table with a row for each code, delete the (many) duplicates
> and then do the select. It was only because I could easily use CONTAINS to find
> the codes which are in the data table that I jumped to the conclusion that NOT
> CONTAINS would give me the missing ones. However, because every code doesn't
> appear in at least one row they all, quite correctly, get returned and,
> incidentally, it takes forever to do it.
>
> Regards,
> Alastair.
>
>
>
> ----- Original Message -----
> From: "Bill Downall" <[EMAIL PROTECTED]>
> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
> Sent: Monday, September 08, 2003 11:19 PM
> Subject: [RBASE-L] - Re: Finding unused id codes
>
>
> > Alastair,
> >
> > SELECT codecolumn FROM lookuptable +
> > WHERE codecolumn NOT IN +
> > (SELECT codecolumn FROM ForeignKeyTable)
> >
> > Bill
> >
> >
> >
> > On Mon, 8 Sep 2003 23:02:27 +0100, Alastair Burr wrote:
> >
> > >Hello everyone,
> >
> > >I'm trying to find what codes from a look-up table are not currently in
> use:
> >
> > >I have a data table that has, amongst other columns, a text column with a
> > >(variable) number of 2 character codes separated by a comma, viz:
> > >aa,ab,ac,ba,bb,bc...
> >
> > >The look-up table has code column with a row for each code and a
> > column with
> > >a description.
> >
> > >I can find which codes are in use easily enough and I thought that the
> > >missing ones could be found by "reversing" the logic but it doesn't work:
> >
> > >Briefly, SELECT... WHERE ... CONTAINS tell me the ones in use but
> > SELECT...
> > >WHERE ... NOT CONTAINS gives me everything.
> >
> > >I can think of a way to do it using a temp table but I wondered if there
> was
> > >an SQL way to find the unused items? Anybody got any ideas?
> >
> > >Thanks in advance,
> > >Regards,
> > >Alastair.
> >
> > >----------------------------------
> > >A D B Burr,
> > >St. Albans, UK.
> > >----------------------------------
> > >[EMAIL PROTECTED]
> > >----------------------------------
> >
>
