how to identify greater than one changes of a field in one day from mainframe DB2 tab

[ppssk]

how to identify greater than one changes of a field in one day from mainframe DB2 table?

For Example i have a field EMP_CODE_NO in EMP table.The Key for the table is EMP_ID. The EMP_CODE_NO is getting changed from 10 to 20.In a same day, it may get changed from 20to 50.How to identify this case from that table by writting SQL query?

Allowable values in EMP_CODE_NO field is 10,20,30,40 and 50.

[Rabbit]

I'm not sure what your question is. Are you wanting to return records where the field was changed more than once in a day? You won't be able to do that unless you create a history table and use a trigger to insert into the history table everytime a change is made.

[vijay2082]

You may want to look out for Temporal table feature introduces in DB2 MF V10.

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_creationoftableswithdataversioning.htm

Prior to this you would have to create history table for the main table and write trigger to store old values with the timestamp and then record the changes.

Cheers, Vijay