SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Join Multiple Versioned Tables

user621430Aug 25 2014 — edited Aug 26 2014

Hi,

We are working on a data warehousing project and am wondering how to join multiple tables each of which are versioned independently(SCD type 2 with a valid from and valid to date).

For eg, we get our client information (client id , name etc) from one source and the client rating information from another source. Since the sources are different,we have separate tables for them and each of them gets versioned independently.

Here is my Client table ( with its own valid from and valid to columns).

ID	Client Name	Valid From	Valid To
1	CitiBank	01-JAN-14	01-JAN-15
1	CitiBank New	02-JAN-15	01-FEB-15
1	CitiBank Newer	02-FEB-15	01-APR-15

And similarly the Client Rating has the ID and the rating information.

ID	Rating	Valid From	Valid To
1	Platinum	01-JAN-14	01-FEB-14
1	Premium	01-FEB-14	01-MAR-15

I would like to merge the above two tables, and present the information in a single view. I am having some difficulty in determing the valid from and valid to columns.

ID	Client Name	Rating	Valid From(Calculated)	Valid To (Calculated)
1	CitiBank	Platinum	01-JAN-14	01-FEB-14
1	CitiBank	Premium	01-FEB-14	01-JAN-15
1	CitiBank New	Premium	02-JAN-15	01-FEB-15
1	CitiBank Newer	Premium	02-FEB-15	01-MAR-15

And this is the query I used to get the above output:

SELECT client.id,

client.name,

crm.tier,

Greatest(client.vld_fm, crm.vld_fm),

Least(client.vld_to, crm.vld_to)

FROM client client,

client_rating crm

WHERE client.id = crm.id

AND ( client.vld_fm <= crm.vld_fm

AND client.vld_fm <= crm.vld_to

AND client.vld_fm >= crm.vld_fm

AND client.vld_fm >= crm.vld_to )

OR ( client.vld_fm BETWEEN crm.vld_fm AND crm.vld_to )

OR ( client.vld_to BETWEEN crm.vld_fm AND crm.vld_to );

The problem really is we have multiple sources of data (and each with its own versioning) and then the joins become very very complex. Is there a better way of writing the query ?

Or perhaps a better way of designing our tables?

Thanks for your help.

Anand

This post has been answered by Marwim on Aug 25 2014

Jump to Answer

Marwim

Answer

Hello,

you only need overlapping ranges to join.

A simpler way to test if the range x_start to x_stop overlaps with the range y_start to y_stop is

WHERE     x_start <= y_stop AND     y_start <= x_stop

That is, two ranges overlap if and only if each one starts before the other one ends. If that's not obvious (and it certainly wasn't obvious to me when I first heard it), then look at it this way: two ranges do not overlap if and only if one of them begins after the other one ends.

Regards

Marcus

Marked as Answer by user621430 · Sep 27 2020

1 person found this helpful

Frank Kulash

Hi, Anand,

As Marcus said, you can find periods where 2 tables overlap like this:

SELECT c.id

, c.client_name

, cr.rating

, GREATEST (c.valid_from, cr.valid_from) AS valid_from_calc

, LEAST (c.valid_to, cr.valid_to) AS valid_to_calc

FROM client c

JOIN client_rating cr ON cr.id = c.id

AND cr.valid_from <= c.valid_to

AND cr.valid_to >= c.valid_from

ORDER BY id

, valid_from_calc

;

In the title of the thread, you mention multiple tables. If you need to join 3 or more tables, and find the periods where all of them overlap, then you can do something like this:

SELECT c.id

, c.client_name

, cr.rating

, t3.label

, GREATEST (c.valid_from, cr.valid_from, t3.valid_from) AS valid_from_calc

, LEAST (c.valid_to, cr.valid_to, t3.valid_to) AS valid_to_calc

FROM client c

JOIN client_rating cr ON cr.id = c.id

AND cr.valid_from <= c.valid_to

AND cr.valid_to >= c.valid_from

JOIN third_table t3 ON t3.id = c.id

AND t3.valid_from <= LEAST (c.valid_to, cr.valid_to)

AND t3.valid_to >= GREATEST (c.valid_from, cr.valid_from)

ORDER BY id

, valid_from_calc

;

depending on your requirements. Adding another table is just a matter of including the new table data in the SELECT clause (including the GREATEST and LEAST expressions), and adding another JOIN to the FROM clause. Notice how the join condition for the 3rd table used GREATEST and LEAST expressions involving the first 2 tables. The join condition for the N-th table (N > 2) will use GREATEST and LEAST involving the first N-1 tables.

I hope this answers your question.

If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

Explain, using specific examples, how you get those results from that data.

Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

See the forum FAQ:

1 person found this helpful

user621430

Thank yo Marwim. That is the most elegant piece of query I have ever seen

Marwim

user621430 wrote:

Thank yo Marwim. That is the most elegant piece of query I have ever seen

But I just quoted Frank from another thread, so it is not really mine :-)

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 23 2014

Added on Aug 25 2014

4 comments

1,256 views

SQL & PL/SQL

Join Multiple Versioned Tables

Comments

Post Details