Re: DB comparison case (in)sensitivity ordering

>> The algorithm selects one record (keyfields only) from each DB with
identical order by clauses. <<

Where to start? There are no "fields" in SQL; tables have columns and columns are very different. Likewise, a table is made up of rows and not records. The reason this is important is that your whole approach is based on a file (i.e. records and fields) approach. But SQL is a set-oriented language.

Next, you did not post any DDL, so we have to make up our own tables, columns, constraints, keys, etc. in order to help you. Based on nothing more than your post, I will guess that the table look like this:

CREATE TABLE Foo
(key_1 CHAR(1) NOT NULL,
 key_2 INTEGER NOT NULL,
 ...
 PRIMARY KEY (key_1, key_2));

CREATE TABLE Bar
(key_1 CHAR(1) NOT NULL,
 key_2 INTEGER NOT NULL,
 ...
 PRIMARY KEY (key_1, key_2));

>> We compare key values and
 if equal
 then
   we have a match
   get next source record
   get next dest record
 elsif source lt dest
 then
   extra record in dest

• action to get record from dest

   get next source record
 else
   missing record in dest

• action to store source record in dest get next dest record end if

The algorithm relies upon identical ordering of source and destination records. <<

This is a tape merge. And sets have no ordering!! You are having to pull out data, sort it then bulk load it back into two databases. You are going to hav eto get the data into a true common format so you can pump it back and forth. Look at Data Junction or other tools. The actual SQL will look somethign like this:

BEGIN Pump data over to table Bar with tool;

INSERT INTO Foo
SELECT *
  FROM Bar
 WHERE NOT EXISTS

       (SELECT *
          FROM Foo
         WHERE Bar.key_1 = Foo.key_1
           AND Bar.key_2 = Foo.key_2);

Delete Bar in the second database;
Pump data over to refill table Bar with tool;

END;
>> if source and dest and some mix of case sensitivity then the
algorithm falls apart. <<

Standard SQL is a case sensitive language; you have set both databases to conform to standards, of course. So what is the problem? Or maybe that is the problem -- look at your settings. SQL Server can be set to Standards pretty easily, but Oracle before 9i was a mess ("A monkey wearing lipstick", to quote an IBM spokesman <g>). Received on Tue Nov 27 2001 - 03:01:46 CET