How to do a NOT IN when selecting table values

KAdamsInCo · 2008-12-05

I know how to do this in SQL:

insert into [dbo].[SAM-TEST$Constituent Category] ([Constituent No_],[Line No_],
[Category Type],[Category Code],[Spouse Related])
select No_,
LineNum = (select isnull((max([Line No_]) + 10000),10000) from [dbo].[SAM-TEST$Constituent Category]
where [Constituent No_] = [dbo].[SAM-TEST$Contact].[No_]),
'DONORSTAT', '', 0
from [dbo].[SAM-TEST$Contact]
where [No_] NOT IN (SELECT [Constituent No_] from [dbo].[SAM-TEST$Constituent Category]
where [Category Type] = 'DONORSTAT');

But how do I do a "NOT IN" in C/SIDE? I know how to apply FILTERs and SETRANGEs and such, but what is the best way to select where a value is NOT IN a list selected from the same table?

Thanks!

#-o

kine · 2008-12-05

There is no simple way. Better will be when you describe by words what you want to do and it will lead to some solution quicker than this "unreadable" SQL command... ;-)

garak · 2008-12-05

You want to fill your table "Constituent Category" based on table Contact.

variables:
ConstCategory Record Constituent Category
Contact Record Contact
LineNo Integer

LineNo := 10000;

//go through the Contacts
Contact.reset;
if Contact.findset then begin
  repeat
    //is there a 'DONORSTAT' category for this contact? If yes, do nothing for this contact
    ConstCategory.reset;
    ConstCategory.setrange("Constituent No.",Contact."No.");
    ConstCategory.setrange("Category Type",'DONORSTAT');
    if ConstCategory.isempty then begin  //No ConstCatogory found
      ConstCategory.setrange("Category Type");
      ConstCategory.locktable;
      if ConstCategory.find('+') then  //find last Line no
        LineNo := ConstCategory."Line No." + 10000;
        
      ConstCategory.init;
      ConstCategory."Constituent No." := Contact."No.";
      ConstCategory."Line No." := LineNo;
      ConstCategory."Category Type" := 'DONORSTAT';
      ConstCategory."Category Code" := '';
      ConstCategory."Spouse Related" := 0;
      ConstCategory.insert;
    end;
  until Contact.next = 0;
end;

Do you mean this?

DenSter · 2008-12-05

Only you don't want to do line number like that

variables:
ConstCategory Record Constituent Category
Contact Record Contact
LineNo Integer

//LineNo := 10000; // this way it's going to increment the number, where it should start at 10000 for each contact

//go through the Contacts
Contact.reset;
if Contact.findset then begin
  repeat
    //is there a 'DONORSTAT' category for this contact? If yes, do nothing for this contact
    ConstCategory.reset;
    ConstCategory.setrange("Constituent No.",Contact."No.");
    ConstCategory.setrange("Category Type",'DONORSTAT');
    if ConstCategory.isempty then begin  //No ConstCatogory found
      ConstCategory.setrange("Category Type");
      ConstCategory.locktable;
//      if ConstCategory.find('+') then  //find last Line no
//        LineNo := ConstCategory."Line No." + 10000;
      if ConstCategory.FINDLAST then  //find last Line no, but use FINDLAST, as we only want 1 record
        LineNo := ConstCategory."Line No." + 10000
      else
        LineNo := 10000;
        
      ConstCategory.init;
      ConstCategory."Constituent No." := Contact."No.";
      ConstCategory."Line No." := LineNo;
      ConstCategory."Category Type" := 'DONORSTAT';
      ConstCategory."Category Code" := '';
      ConstCategory."Spouse Related" := 0;
      ConstCategory.insert;
    end;
  until Contact.next = 0;
end;

garak · 2008-12-05

or so with the "line no."
I increment the "line No." because i doesn't know his Primary key structure. And in his SQL query he also increment (intended or not intended)

DenSter · 2008-12-05

Sure, point taken

My assumption was: primary key = Contact No, Line No

KAdamsInCo · 2008-12-15

This example code worked perfectly. Thank you for taking the time to post the reply, it really helped!

-Keaton

=D>

DenSter · 2008-12-15

We aim to please

Glad you were able to make that work.

garak · 2008-12-15

Please and welcome

How to do a NOT IN when selecting table values

Answers

Categories