Home » SQL & PL/SQL » SQL & PL/SQL » display Old value and new value as per date in single row
display Old value and new value as per date in single row [message #677662] Thu, 03 October 2019 19:34 Go to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Team,

Request you please help me with the sql that will display old value and new value as desired below.

Scenario1:

Table data as below:


Date               Action              Test_message
10/01/2019          A                SETID equal to USAID
10/01/2019          A                AND VENDOR_ID equal to 567843

Output should be as below:
old_value                     New_value
                            SETID equal to USAID
                            AND VENDOR_ID equal to 567843


Scenario2:

Table data as below:


Date               Action              Test_message
10/01/2019          A                SETID equal to USAID
10/01/2019          A                AND VENDOR_ID equal to 567843
10/02/2019          A                AND STATUS equal to A
10/02/2019          A                AND NAME  equal to JHON
10/02/2019          A                AND ADDRESS equal to NEWYORK


Output should be as below:
old_value                                    New_value
SETID equal to USAID                   SETID equal to USAID
AND VENDOR_ID equal to 567843          AND VENDOR_ID equal to 567843
                                       AND STATUS equal to A
                                       AND NAME  equal to JHON
                                       AND ADDRESS equal to NEWYORK

Scenario3:

Table data as below:


Date               Action              Test_message
10/01/2019          A                SETID equal to USAID
10/01/2019          A                AND VENDOR_ID equal to 567843
10/02/2019          A                AND STATUS equal to A
10/02/2019          A                AND NAME  equal to JHON
10/02/2019          A                AND ADDRESS equal to NEWYORK
10/03/2019          D                AND STATUS equal to A
10/03/2019          D                AND NAME  equal to JHON
10/03/2019          D                AND ADDRESS equal to NEWYORK
10/03/2019          A                AND NAME  equal to JHON
10/03/2019          A                AND ADDRESS equal to NEWYORK

Output should be as below:
old_value                                    New_value
SETID equal to USAID                   SETID equal to USAID
AND VENDOR_ID equal to 567843          AND VENDOR_ID equal to 567843
AND STATUS equal A 
AND NAME  equal to JOHN                AND NAME  equal to JOHN
AND ADDRESS equal to  NEWYORK          AND ADDRESS equal to NEWYORK

Note: here action A means ADD and D means DELETE.
Whenever I delete any row from the table the next subsequent rows will change their position and hence it will create 2 different rows for the same text_message with different actions. Hence in the above scenario, I deleted 3rd row and hence 4th row become 3rd, and 5th row become 4th row in my original table.

All previous date values always would be under old value column and new value column should have rows newly added.

Thank you.

Regards
Suji
Re: display Old value and new value as per date in single row [message #677691 is a reply to message #677662] Mon, 07 October 2019 07:11 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Oracle doesn't keep information on the order of entry in a table so if you have the following rows

10/01/2019 A SETID equal to USAID
10/01/2019 A AND VENDOR_ID equal to 567843

It has no way to determine if "SETID equal to USAID+ was entered first or "AND VENDOR_ID equal to 567843" was entered first. Your date information doesn't have time information, just date. one way to hanle that would be to use an identity column (auto populated seq number using an oracle sequence) which would show entry order.
Previous Topic: Calculation
Next Topic: Aggregate operation on array - get totals
Goto Forum:
  


Current Time: Thu Mar 28 10:48:27 CDT 2024