5 Replies Latest reply: Jun 9, 2014 7:10 PM by 1014077 RSS

    Get latest record among multiple tables

    1014077

      Hello Guys,

       

      I would really appreciate if you could help me in figuring out a solution to this problem I am facing:

       

      We are on Oracle 11g Release 2.

       

      Say, I have 3 tables A, B and C, all the 3 tables have the following columns with the exact same names:

       

      data_load_time DATE

      data_load_process VARCHAR2(100)

      data_update_time DATE

      data_update_process VARCHAR2(100)

       

      And, also a foreign key - fkey NUMBER.

       

      Now the requirement is - for a given fkey (say 100), I need to pull the latest time and process among the 3 tables, it doesn't matter whether

      it's load or update columns, I just need 1 set of values, the max time and the corresponding process out.

       

      Eg: Say for fkey = 100, I get the max time and the corresponding process from table A, then I get the max time and corresponding process from table B and then from table C, the final result should have the max time and the corresponding process out of the 3 values I just pulled out from the 3 tables separately.

       

      Is there a efficient way of accomplishing this in just 1 query ?.

       

      Thanks for your help.

      -D

        • 1. Re: Get latest record among multiple tables
          Ramin Hashimzadeh

          this?

           

          select greatest(max(a.data_load_time),max(b.data_load_time),max(c.data_load_time)) max_data_load_time,

                 decode(greatest(max(a.data_load_time),max(b.data_load_time),max(c.data_load_time)), max(a.data_load_time), max(a.data_load_process) keep (dense_rank first order by a.data_load_time desc),

                                                                                                     max(b.data_load_time), max(b.data_load_process) keep (dense_rank first order by b.data_load_time desc),

                                                                                                     max(c.data_load_time), max(c.data_load_process) keep (dense_rank first order by c.data_load_time desc)

                       ) data_load_process,            

                 greatest(max(a.data_update_time),max(b.data_update_time),max(c.data_update_time)) max_data_update_time,

                 decode(greatest(max(a.data_update_time),max(b.data_update_time),max(c.data_update_time)), max(a.data_update_time), max(a.data_load_process) keep (dense_rank first order by a.data_update_time desc),

                                                                                                     max(b.data_update_time), max(b.data_update_process) keep (dense_rank first order by b.data_update_time desc),

                                                                                                     max(c.data_update_time), max(c.data_update_process) keep (dense_rank first order by c.data_update_time desc)

                       ) data_update_process

           

          from a

           

          join b

          on a.fkey = b.fkey

           

          join c

          on a.fkey = c.fkey

           

          where fkey = 100

           

          ----

          Ramin Hashimzade

          • 2. Re: Get latest record among multiple tables
            1014077

            Hello Ramin,

             

            Thanks for the quick response, I have not run your query yet but looking at it, you have used MAX function on the VARCHAR2 datatype, not sure if that will help me, I am going to test with your query soon, but here is a better example of my issue with example data:

            Table A

            fkey | data_load_time | data_load_process | data_update_time | data_update_process

            ---------------------------------------------------------------------------------------------------------------------------

            100  | 01 - JAN - 2013   | XETL                       | 11 - JULY - 2013     | MANUAL

             

            Table B

            fkey | data_load_time | data_load_process | data_update_time | data_update_process

            ---------------------------------------------------------------------------------------------------------------------------

            100  | 05 - JAN - 2013   | DATA_ENT              | 25 - APR - 2014      | VEND_X

             

            Table C

            fkey | data_load_time | data_load_process | data_update_time | data_update_process

            ---------------------------------------------------------------------------------------------------------------------------

            100  | 01 - JAN - 2014   | MANUAL                 | 03 - FEB - 2014      | CLM_PROC

             

            Now, with the above example data, I need the following result:

             

            fkey |         time         | process |

            ------------------------------------------------
            100  | 25 - APR - 2014 | VEND_X

             

            Thanks,

            D

            • 3. Re: Get latest record among multiple tables
              indra budiantho

              /* Formatted on 6/7/2014 9:22:05 AM (QP5 v5.139.911.3011) */

              WITH t_A AS (SELECT 100 fkey,

                                  '01-JAN-2013' data_load_time,

                                  'XETL' data_load_process,

                                  '11-JUL-2013' data_update_time,

                                  'MANUAL' data_update_process

                             FROM DUAL),

                   t_B AS (SELECT 100 fkey,

                                  '05-JAN-2013' data_load_time,

                                  'DATA_ENT' data_load_process,

                                  '25-APR-2014' data_update_time,

                                  'VEND_X' data_update_process

                             FROM DUAL),

                   t_c AS (SELECT 100 fkey,

                                  '01-JAN-2014' data_load_time,

                                  'MANUAL' data_load_process,

                                  '03-FEB-2014' data_update_time,

                                  'CLM_PROC' data_update_process

                             FROM DUAL)

              SELECT fkey, LOAD_UPD_TIME, DATA_LOAD_UPD

                FROM (SELECT fkey,

                             LOAD_UPD_TIME,

                             DATA_LOAD_UPD,

                             ROW_NUMBER ()

                                OVER (PARTITION BY FKEY ORDER BY LOAD_UPD_TIME DESC)

                                rn

                        FROM (SELECT fkey,

                                     TO_DATE (data_load_time, 'DD-MON-RRRR') LOAD_UPD_TIME,

                                     data_load_process DATA_LOAD_UPD

                                FROM T_A

                               WHERE fkey = 100

                              UNION

                              SELECT fkey,

                                     TO_DATE (data_update_time, 'DD-MON-RRRR'),

                                     data_update_process

                                FROM T_A

                               WHERE fkey = 100

                              UNION

                              SELECT fkey,

                                     TO_DATE (data_load_time, 'DD-MON-RRRR'),

                                     data_load_process

                                FROM T_B

                               WHERE fkey = 100

                              UNION

                              SELECT fkey,

                                     TO_DATE (data_update_time, 'DD-MON-RRRR'),

                                     data_update_process

                                FROM T_B

                               WHERE fkey = 100

                              UNION

                              SELECT fkey,

                                     TO_DATE (data_load_time, 'DD-MON-RRRR'),

                                     data_load_process

                                FROM T_C

                               WHERE fkey = 100

                              UNION

                              SELECT fkey,

                                     TO_DATE (data_update_time, 'DD-MON-RRRR'),

                                     data_update_process

                                FROM T_C

                               WHERE fkey = 100)) domains

              WHERE rn = 1

              • 4. Re: Get latest record among multiple tables
                Ramin Hashimzadeh

                Use my query and you will see tghat everything is correct and don't worry about max function (read about analytical functions keep dense_rank first/last)

                 

                here is result of my query with your example data :

                 

                WITH A AS

                (SELECT 100 fkey,

                         '01-JAN-2013' data_load_time,

                         'XETL' data_load_process,

                         '11-JUL-2013' data_update_time,

                         'MANUAL' data_update_process

                    FROM DUAL),

                B AS

                (SELECT 100 fkey,

                         '05-JAN-2013' data_load_time,

                         'DATA_ENT' data_load_process,

                         '25-APR-2014' data_update_time,

                         'VEND_X' data_update_process

                    FROM DUAL),

                c AS

                (SELECT 100 fkey,

                         '01-JAN-2014' data_load_time,

                         'MANUAL' data_load_process,

                         '03-FEB-2014' data_update_time,

                         'CLM_PROC' data_update_process

                    FROM DUAL)

                 

                select greatest(max(a.data_load_time),

                                max(b.data_load_time),

                                max(c.data_load_time)) max_data_load_time,

                     

                       decode(greatest(max(a.data_load_time),

                                       max(b.data_load_time),

                                       max(c.data_load_time)),

                              max(a.data_load_time),

                              max(a.data_load_process)

                              keep(dense_rank first order by a.data_load_time desc),

                            

                              max(b.data_load_time),

                              max(b.data_load_process)

                              keep(dense_rank first order by b.data_load_time desc),

                            

                              max(c.data_load_time),

                              max(c.data_load_process)

                              keep(dense_rank first order by c.data_load_time desc)

                            

                              ) data_load_process,

                     

                       greatest(max(a.data_update_time),

                                max(b.data_update_time),

                                max(c.data_update_time)) max_data_update_time,

                     

                       decode(greatest(max(a.data_update_time),

                                       max(b.data_update_time),

                                       max(c.data_update_time)),

                              max(a.data_update_time),

                              max(a.data_load_process)

                              keep(dense_rank first order by a.data_update_time desc),

                            

                              max(b.data_update_time),

                              max(b.data_update_process)

                              keep(dense_rank first order by b.data_update_time desc),

                            

                              max(c.data_update_time),

                              max(c.data_update_process)

                              keep(dense_rank first order by c.data_update_time desc)

                            

                              ) data_update_process

                 

                  from a

                 

                  join b

                    on a.fkey = b.fkey

                 

                  join c

                    on a.fkey = c.fkey

                 

                where a.fkey = 100

                 

                SQL>

                60  /

                MAX_DATA_LOAD_TIME               DATA_LOAD_PROCESS                MAX_DATA_UPDATE_TIME             DATA_UPDATE_PROCESS

                -------------------------------- -------------------------------- -------------------------------- --------------------------------

                05-JAN-2013                      DATA_ENT                         25-APR-2014                      VEND_X

                 

                SQL>

                 

                 

                ----

                Ramin Hashimzade

                • 5. Re: Get latest record among multiple tables
                  1014077

                  Thank you Ramin, it worked.