Re: Find the difference across records

Thank you for pointing me in the direction of CASE. However, let me add a new element. How would I go about prioritizing an UPDATE. For instance, if I have two records (from my previous two tables), how do I compare the two records, field by field.

to illustrate:

               CUSTID     LNAME     FNAME   DOB     
               --------------------------------------
Record 1>>     12345                JOHN    12/15/74
Record 2>>     12345      SMITH     JOHN    

Sometimes, an update is not so clear cut. How would I build a sql statement that allows me to use the DOB in Record 1 (because it is filled), but the LNAME in Record 2 (because it is filled)?

Thanks.

flatline_at_mailbox.hu wrote in message news:<bc3a43ba.0211160656.558d88c7_at_posting.google.com>...
> Hello! I'm not sure that i clearly understand your purpose, but lets
> assume that there is a mother table (CUSTOMERS) and a driving table
> (CHANGES) with identical structure and primary key on CUSTID, and not
> null constraint on all columns
> The importance of the update is determined by weighting the changes
> (for example in this case in order of columns definition)
>
>
>
> create table UPDATES as
> select custid,
> case when a.lname<>b.lname then 1 else 0 end+
> case when a.fname<>b.fname then 2 else 0 end+
> case when a.acq_date<>b.acq_date then 4 else 0 end+
> case when a.branch<>b.branch then 8 else 0 end URGENCY
> from CUSTOMERS a,CHANGES b where a.custid=b.custid;
>
> will create a driving table that contains CUSTID and URGENCY columns
> you may want to insert the new records into this table with URGENCY
> code of 16 (most urgent)
>
> insert into UPDATES
> select custid,16 URGENCY
> from CUSTOMERS a,CHANGES b where b.custid=a.custid(+) and a.custid is
> null;
>
> You may process updates and inserts now according to the URGENCY code,
> presumably descending.
>
> The above queries WERE NOT SYNTACTICALLY checked.. Sorry for possible
> errors ;)
>
> HTH, Regards
> Flatline
>
>
> mercadop_at_expertx.com (Pedro Mercado) wrote in message news:<bb425dee.0211141245.245ce9c9_at_posting.google.com>...
> > Hello,
> >
> > We have a customer database that gets updated every week with Change
> > or Add records. We receive files with various fields. Suppose, we
> > get a Customer ID that matches our database. Is there a way to
> > develop a query or plsql that will tell us the fields that are
> > different between the two records?
> >
> > For example, suppose we have the following two records:
> >
> > CUSTID LNAME FNAME DOB ACQ_DATE BRANCH
> > ------------------------------------------------------------
> > 12345 SMITH JOHN 12/15/74 01/01/02 1178
> > 12345 SMITH JOHN 01/01/02 1187
> >
> > Is there a way to determine that the DOB and BRANCH fields are
> > different for this customer? This is very important for our process
> > in order to determine the priority level of an update. We receive too
> > many records to visually inspect them all. Please help.
> >
> > Thank You,
> > Pedro Mercado
> > mercadop_at_expertx.com
Received on Mon Nov 18 2002 - 19:17:43 CET