0

I have a table that contains the history of Customer IDs that have been merged in our CRM system. The data in the historical reporting Oracle schema exists as it was when the interaction records were created. I need a way to find the Current ID associated with a customer from potentially an old ID. To make this a bit more interesting, I do not have permissions to create PL/SQL for this, I can only create Select statements against this data.

Sample Data in customer ID_MERGE_HIST table

| OLD_ID   | NEW_ID   |
+----------+----------+
| 44678368 | 47306920 |
| 47306920 | 48352231 |
| 48352231 | 48780326 |
| 48780326 | 50044190 |

Sample Interaction table

| INTERACTION_ID | CUST_ID  |
+----------------+----------+
| 1              | 44678368 |
| 2              | 48352231 |
| 3              | 80044190 |

I would like a query with a recursive sub-query to provide a result set that looks like this:

| INTERACTION_ID | CUST_ID  | CUR_CUST_ID |
+----------------+----------+-------------+
| 1              | 44678368 | 50044190    |
| 2              | 48352231 | 50044190    |
| 3              | 80044190 | 80044190    |

Note: Cust_ID 80044190 has never been merged, so does not appear in the ID_MERGE_HIST table.

Any help would be greatly appreciated.

BarryTheSprout
  • 13
  • 4

1 Answers1

0

You can look at CONNECT BY construction.

Also, you might want to play with recursive WITH (one of the descriptions: http://gennick.com/database/understanding-the-with-clause). CONNECT BY is better, but ORACLE specific.

If this is frequent request, you may want to store first/last cust_id for all related records.

First cust_id - will be static, but will require 2 hops to get to the current one

Last cust_id - will give you result immediately, but require an update for the whole tree with every new record

vav
  • 4,584
  • 2
  • 19
  • 39
  • I actually started with Connect By, but found it was giving me a row back for each iteration. Seeing you use the term First and Last actually made me think of the aggregate capabilities in Oracle and I want to try wrapping the Connect By select in another select using Last. Will let you know where that gets me. – BarryTheSprout Oct 15 '14 at 12:16
  • If you have control over database design, this link might be useful: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – vav Oct 16 '14 at 19:25