Re: Find the difference across records
Date: 16 Nov 2002 06:56:14 -0800
Message-ID: <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
create table UPDATES as
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
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>...
The importance of the update is determined by weighting the changes
(for example in this case in order of columns definition)
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;
select custid,16 URGENCY
from CUSTOMERS a,CHANGES b where b.custid=a.custid(+) and a.custid is
null;
> 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
>
> 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 Sat Nov 16 2002 - 15:56:14 CET