0

I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify the table structure as an example.

I have 2 tables I am working with. Each is an audit table which keeps track of changes such as updates and inserts via a trigger. Each table also has a column to store the date of change.

One table contains a county code, the other contains the street address: t_county and t_address

t_county

  • id: NUMBER(9)

  • county: NUMBER(2)

  • date: DATE

t_address

  • id: NUMBER(9)

  • address: VARCHAR2(60)

  • date: DATE

I need to build a query which selects the county and address if either one has a row on a specific day.

Say there is a change of address on a specific day, but there is no change in the county. I would like for the query to have a result such as below

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  NULL   |123 test|
+---------+---------+--------+

If there is a change of county but the address is the same:

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  32     |  NULL  |
+---------+---------+--------+

How can I build such a query?

3
  • I think I might need a Right Join but now sure how I can implement this.
    – Jose Ortiz
    Aug 31 '18 at 21:45
  • DATE is a keyword. Don't use it as a Column Name. Sep 1 '18 at 12:09
  • Like I said, it is just a toy example. These are not the actual tables. The actual structure are established.
    – Jose Ortiz
    Sep 1 '18 at 21:23
0

Here's one option, which uses UNION of those tables. Aggregate (MAX) is used to display changes in both county and address in a single row; otherwise, you'd get two separate rows. This won't work correctly if there are several changes within a single date.

SQL> with t_county (id, county, cdate) as
  2    (select 1, 10, date '2018-08-02' from dual union all
  3     select 2, 20, date '2018-08-05' from dual
  4    ),
  5  t_address (id, address, cdate) as
  6    (select 1, '123 test', date '2018-08-10' from dual union all
  7     select 2, '222 twix', date '2018-08-05' from dual
  8    ),
  9  -- union of those tables
 10  t_union as
 11    (select id, county     , null address, cdate from t_county union all
 12     select id, null county, address     , cdate from t_address
 13    )
 14  select id,
 15    max(county) county,
 16    max(address) address
 17  from t_union
 18  where cdate = to_date('&par_date', 'yyyy-mm-dd')
 19  group by id;
Enter value for par_date: 2018-08-02

        ID     COUNTY ADDRESS
---------- ---------- --------
         1         10

SQL> /
Enter value for par_date: 2018-08-05

        ID     COUNTY ADDRESS
---------- ---------- --------
         2         20 222 twix

SQL>
1
  • I kept iterating through many solutions but since in my case, there are multiple changes per day per id, this solution will not work correctly. However, in the cases that there is only a single row, this solution is perfect, therefore, I will mark this as the solution. Thank you very much for your help. I was forced to find a compromise for the solution but your solution actually help me out. Thank you again.
    – Jose Ortiz
    Sep 5 '18 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.