SQL help

[OldtTimerDon]

Assume that I have two name and address tables: mynew and myold. Let's populate both of them with client, zip and phoneno (and other fields).

I seek a query that would produce a listing of old clients who are not in the new clients file.

Select client, zip, phoneno from myold where client not in(select client, zip, phoneno from mynew order by client) into cursor missingdata is an unsupported query. How can I accomplish my goal?

There must be a better way than doing a query that produces a cursor of records that are common to both files. I could then do a scan that compares to the two files and creates a table of those that are not in the mynew file. Anybody have a better solution?

Senile Senior Don
Sun City, AZ

 

[Olaf Doschke]

INDEX ON client+zip+phoneno TAG xUnique UNIQUE
Select mynew
Append From myold

All double records are now in the table, but suppressed by the index, which always only accepts the first record evaluating to the same overall index expression.

And now you can delete doublettes:

SET ORDER TO
DELETE ALL && deletes all
SET ORDER TO xUnique
RECALL ALL && recalls only first occurrences, which are part of the index
DELETE TAG xUnique

An SQL solution would involve some key field, you can also join by multiple fields, but it's always awkward and tedious.

The problem is, if you have a candidate or primary index on something which prevents doublettes in the first place. On the other hand that's also a way to prevent them in the first place...

Bye, Olaf.

 

[Olaf Doschke]

...by the way, after you did all that you can verify doublettes as doublettes by SET DELEETED OFF and browsng all records deleted or not. If you do that in order of client+zip+phoneno you will see deleted doublettes right below original (older) data and see if there is a change in data you'd want to preserve, eg a new lastname (because of marriage) or anything like that.

Otherwise you can now PACK to get rid of the doulbettes, and then add a candidate index preventing further doublettes.

Bye, Olaf.

 

[JRB-Bldr]

Select client, zip, phoneno from myold where client not in(select client, zip, phoneno from mynew order by client) into cursor missingdata is an unsupported query. How can I accomplish my goal?

It is an Unsupported Query because the Sub-query can only be used for a single field value.

Maybe something like where the Sub-query is only returning a single field:

SELECT client,;
  zip,;
  phoneno;
  FROM myold;
  WHERE client NOT IN (SELECT client FROM mynew);
  ORDER BY Client;
  INTO CURSOR missingdata

But, having said that, if the selection criteria needs to be more complex, the Sub-Query's single field value can also be a complex one.

Such as something like:

SELECT client,;
  zip,;
  phoneno;
  FROM myold;
  WHERE (client + zip + phoneno) NOT IN (SELECT (client + zip + phoneno) FROM mynew);
  ORDER BY Client;
  INTO CURSOR missingdata

Good Luck,
JRB-Bldr