Forum Stats

  • 3,768,293 Users
  • 2,252,772 Discussions
  • 7,874,519 Comments

Discussions

First rec of the last group!!!

Thirumoorthy Selvaraj
Thirumoorthy Selvaraj Member Posts: 19 Red Ribbon
edited Jul 13, 2021 8:15PM in SQL & PL/SQL

Hi All

Any help for me to resolve this issue will be greatly appreciated.

I have data like this below

My history table data looks like

My current table data is

I need the SQL output that has Add date of 8/1/2020 (SNO : 6). I have RecVal and IND to match and use change_date to start my search from my current table data for the RecVal..

I need to search from the bottom of my history list and get to data that has same RecVal and IND of my current table record and get the minimum add_date based on my change_date.

I dont want history records 1 or 2 based on RecVal and IND

Best Answer

  • mathguy
    mathguy Member Posts: 10,154 Blue Diamond
    edited Jul 14, 2021 5:10PM Accepted Answer

    The solution below assumes RECVAL and IND are non-null in both tables. If NULL may appear in those columns, you will have to tell us how that should be handled. I am also assuming that CHANGE_DATE is unique (no duplicates).

    with
      t (recval, ind, qval, add_date, change_date, curr_recval, curr_ind) as (
        select h.recval, h.ind, h.qval, h.add_date, h.change_date, c.recval, c.ind
        from   hist_table h join curr_table c
                            on   h.change_date <= c.change_date
      )
    select recval, ind, qval, add_date, change_date
    from   t
    match_recognize(
      order     by change_date desc
      all rows  per match
      pattern   ( ^ {- a* b* -} b )
      define    a as recval != curr_recval or  ind != curr_ind,
                b as recval  = curr_recval and ind  = curr_ind
    );
    
    
    
    RECVAL     IND QVAL ADD_DATE    CHANGE_DATE
    ---------- --- ---- ----------- -----------
    TEST01     Y   P    01-AUG-2020 15-AUG-2020
    
    Thirumoorthy SelvarajUser_WI23P

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    Hi, @Thirumoorthy Selvaraj

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

    Define any non-standard terms you use. For example, What is a "record"? How does it differ from a row?

    If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

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

    My current record is

    I don't see that row in the sample data.

    Thirumoorthy Selvaraj
  • Thirumoorthy Selvaraj
    Thirumoorthy Selvaraj Member Posts: 19 Red Ribbon
    edited Jul 13, 2021 7:48PM

    Added Creates and Insert statements


     CREATE TABLE "HIST_TABLE" 

     ( "RECVAL" VARCHAR2(10 BYTE), 

       "IND" VARCHAR2(1 BYTE), 

       "QVAL" VARCHAR2(4 BYTE), 

       "ADD_DATE" DATE, 

       "CHANGE_DATE" DATE

     );


    Insert into HIST_TABLE values ('TEST01','Y','A',to_date('05-APR-20','DD-MON-RR'),to_date('05-MAY-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','B',to_date('05-MAY-20','DD-MON-RR'),to_date('01-JUN-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST02','N','X',to_date('01-JUN-20','DD-MON-RR'),to_date('10-JUN-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST02','N','Y',to_date('10-JUN-20','DD-MON-RR'),to_date('01-JUL-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','N','Z',to_date('01-JUL-20','DD-MON-RR'),to_date('01-AUG-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','P',to_date('01-AUG-20','DD-MON-RR'),to_date('15-AUG-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','Q',to_date('15-AUG-20','DD-MON-RR'),to_date('10-SEP-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','R',to_date('10-SEP-20','DD-MON-RR'),to_date('01-NOV-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','S',to_date('01-NOV-20','DD-MON-RR'),to_date('02-DEC-20','DD-MON-RR'));



    CREATE TABLE "CURR_TABLE" 

     ( "RECVAL" VARCHAR2(10 BYTE), 

      "IND" VARCHAR2(1 BYTE), 

      "QVAL" VARCHAR2(4 BYTE), 

      "CHANGE_DATE" DATE

     );


    Insert into CURR_TABLE values ('TEST01','Y','Q',to_date('02-DEC-20','DD-MON-RR'));

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    edited Jul 13, 2021 7:58PM

    Hi,

    Would you like to get answers that work? Make sure that the CREATE TABLE and INSERT statements you post for the sample data work, too. Test (and, if necessary, fix) them before you post them.

    Don't forget to define any non-standard terms you use, such as "record" and "break" and "based on my change_date".

    Always give your full Oracle version.

  • mathguy
    mathguy Member Posts: 10,154 Blue Diamond
    edited Jul 14, 2021 5:10PM Accepted Answer

    The solution below assumes RECVAL and IND are non-null in both tables. If NULL may appear in those columns, you will have to tell us how that should be handled. I am also assuming that CHANGE_DATE is unique (no duplicates).

    with
      t (recval, ind, qval, add_date, change_date, curr_recval, curr_ind) as (
        select h.recval, h.ind, h.qval, h.add_date, h.change_date, c.recval, c.ind
        from   hist_table h join curr_table c
                            on   h.change_date <= c.change_date
      )
    select recval, ind, qval, add_date, change_date
    from   t
    match_recognize(
      order     by change_date desc
      all rows  per match
      pattern   ( ^ {- a* b* -} b )
      define    a as recval != curr_recval or  ind != curr_ind,
                b as recval  = curr_recval and ind  = curr_ind
    );
    
    
    
    RECVAL     IND QVAL ADD_DATE    CHANGE_DATE
    ---------- --- ---- ----------- -----------
    TEST01     Y   P    01-AUG-2020 15-AUG-2020
    
    Thirumoorthy SelvarajUser_WI23P