Need help with a database view between CHG records and sys_history_line

[Matthew20]

Hello,

I am trying to create a data base view to combine the history of the changes(from the audit table sys_history_line from in the related tab screenshot attached) to a field from the CHG record with the rest of the CHG record for easier reporting.

The goals is to be able to let the user in a report search by anything from the CHG record itself or the associated updates to the field mentioned on the form which would be comming from the sys_history_line table.

My most recent database view attempt is attached but i dont really know what im doing. I am not able so far to get anything to show in the database view. Can anyone provide a sample for a solution.

Thanks in advance for your help. Screenshots of the information for more context attached

[Brian Lancaster]

It looks like you would need all three tables in the database view. I cannot figure out how to get them to join properly in the database view. I don't think this is a good solution. Can you give us more details on what you are trying to archive? 

[Matthew20]

Essentially the users big concern is reporting. Obviously they can report from CHG easily and I can create a report to allow them to look at the changes to that POLICY STATUS field pulled from the audit history. What they really want is to be able to report on both from one easy solution. So a report that shows CHG but also the individual POLICY STATUS(old, new, number, user, date) history, realted to the CHG records in the same place.  I imagined ralting them by the sys_id in the DB view and then grouping them by the CHG record maybe on the report? still not sure about that part and maybe your right its just not a good solution.

[Brian Lancaster]

So you have a custom field called policy status and they want to be able to report on the changes to it throughout the lifecycle of the change? If that is the case I would use the metric definition as @Ivan Betev suggested.

[Matthew20]

I understand and i did consider that and i thank you all for the help but they have other needs that are forcing me to try this way. Im trying to make the best of a situation that I walked into. Is the consensus that the DB view between the CHG table and the audit tables just wont work as I hoped?