How to do a NOT IN when selecting table values

KAdamsInCo

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

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

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

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

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

Sure, point taken My assumption was: primary key = Contact No, Line No

KAdamsInCo

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

-Keaton


=D>

DenSter

We aim to please Glad you were able to make that work.

garak

Please and welcome