4 Replies Latest reply: Aug 26, 2014 1:53 AM by Marwim RSS

    Join Multiple Versioned Tables

    user621430

      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).

       

      IDClient NameValid FromValid To
      1CitiBank01-JAN-1401-JAN-15
      1CitiBank New02-JAN-1501-FEB-15
      1CitiBank Newer02-FEB-1501-APR-15

       

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

       

      IDRatingValid FromValid To
      1Platinum01-JAN-1401-FEB-14
      1Premium01-FEB-1401-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.

       

      IDClient NameRatingValid From(Calculated)Valid To (Calculated)
      1CitiBankPlatinum01-JAN-1401-FEB-14
      1CitiBankPremium01-FEB-1401-JAN-15
      1CitiBank NewPremium02-JAN-1501-FEB-15
      1CitiBank NewerPremium02-FEB-1501-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

        • 1. Re: Join Multiple Versioned Tables
          Marwim

          Hello,

           

          you only need overlapping ranges to join.

          Re: how to loop sql query

          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

          • 2. Re: Join Multiple Versioned Tables
            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: Re: 2. How do I ask a question on the forums?

            • 3. Re: Join Multiple Versioned Tables
              user621430

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

              • 4. Re: Join Multiple Versioned Tables
                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 :-)