Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Change log query)
Query Help [message #275517] Sun, 21 October 2007 00:31 Go to next message
sumachaa
Messages: 1
Registered: October 2007
Junior Member

Hi,
im new to oracle. please help me with the below query

Below mentioned table to store all the change log information regarding an order for a all customers.

i.e what value was changed for that field and to what value its changed, the datetime when in happened

CUSTOMER_ID VARCHAR
ORDER_NO VARCHAR
FIELD_CHANGED VARCHAR
CHANGED_ON DATE_TIME
OLD_VALUE VARCHAR
NEW_VALUE VARCHAR

This table will store all values changed for a particular field, i.e for an order, for a particular field how many times the value was changed,

CUSTOMER_ID ORDER_NO FIELD_CHANGED CHANGED_ON OLD_VALUE NEW_VALUE

ABC 100 PURCHASE_TYPE 2007-01-01 10:43:12 DEV DEV_TEST
ABC 100 ORDER_GRP 2007-02-03 23:12:12 SALES PREPAID
ABC 100 PURCHASE_TYPE 2006-02-02 12:12:12 COLD DEV
EFY -- --- --- --- ---
--- --- --- -- --- ---

Now the help I need is to get the values changed for a particular customer, for all orders, for all field the last value only (which we can know from the changed_on field)

i.e for a particular order there can be many rows for the same field, I may have changed it many times, but I need only the last one

as from the example we can see that PURCHASE_TYPE field was changed 2 times, for the same order. But my query should return the last one only

the database used is oracle 10g.

let me know if u have any more questions.

thanks
Sumesh
Re: Query Help [message #275518 is a reply to message #275517] Sun, 21 October 2007 00:55 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What you want is the first row if you partition the table rows by customer_id, order_no and field_changed and order these partitions by changed_on date in descending order.

The ROW_NUMBER, RANK or DENSE_RANK functions will help you.

By the way, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Previous Topic: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables
Next Topic: Prevent duplicate data entry
Goto Forum:
  


Current Time: Mon Feb 10 11:47:19 CST 2025