2 Replies Latest reply on Feb 22, 2013 10:58 AM by aschilling

    Identifying and displaying only changed columns using Workspace manager

      Hi All,
      I am sure many people come across this requirement. I am trying to figure out procedure/function using WM, to identify only changed fields between two rows.

      My original question similar to below thread.

      recovering changes maded in a versioned table

      Solution given in that thread is a table join and showing all fields. But, I need to show ONLY changed fields and their values.

      Experts, is there Any way out utilizing WM views and functions?
        • 1. Re: Identifying and displaying only changed columns using Workspace manager
          Stefan Jager
          Hi user498892,

          Have you read the thread you link to? There is no join being made in that thread, just a view across the ###_HIST view. I think the View is an excellent one, and one that you could easily use/modify.

          Secondly, Workspace Manager tracks the changes at row-level, not column level. So you'll get the changed rows, not the changed columns by default. Your requirement to only show the changed columns could be done by comparing the History versions. If the value hasn't changed, the column hasn't changed. Shouldn't be too difficult to create, I think.

          As an afterthought: Why do you want to it it this way? If you know the reason why, there may be other ways of achieving what it is you want.

          • 2. Re: Identifying and displaying only changed columns using Workspace manager
            There actually is a join :-)
            But apart from that: with a view or table you can't get only changed columns as that would result in different columns to select for each changed row. Or, maybe with some sophisticated PL/SQL and query magic you even might be able to do so, but probably only on a row-per-row base and not at once like with the default _HIST views.
            As Stefan mentioned, depending on how you access the data it's quite easy to sort those out. We're e.g. using Hibernate from a Java application and simply map the columns to two different objects embedded in a "history container object". Thus we have two historical instances giving us the old and new state at once. Then you can simply compare those property-by-property (that is, column by column) and get the differences.
            One should mention that such a setup is only good to handle when you have sort of a model-driven process so that you can easily react to schema changes and have code generated that implements the functionality. Changing view definitions, mappings, java classes, data access code and the logic by hand everytime you add/remove/change columns or even complete tables surely is no fun (and error prone as well)