Database view for incident and ar_incident tables

[User175049]

Is it possible to join an archive table in a database view? 

I have a request to create a report that would include records on the archive table and the incident table.  Creating a report for both tables is preferred over changing our Archive Rules for Incident.   The database view seems like the best option, and I am attempting to create one, but the database view table is missing records.

Any help is appreciated.

Thank you

[User175049]

Here is the screenshot of what I have so far. I found this order provides the most records (130,046) but still not the entire list I am looking for.

Thank you

[Bert_c1]

I don't see a 'where' clause to link the two tables.  Since ar_incident is for Archiving incident records, and contains records from the incident table, why is a database view needed?

see:

Archive

I understand what want now, try setting Left join to true on both. I'll test in my instance soon.

[Bert_c1]

Hi User175049,

I can't get a database view to work, to show all records in both tables. I don't see anyway to join the two tables. Seems you'll need to restore archived records, but I expect you don't want to do that.

[User175049 · Accepted solution]

Thanks all for your inputs.  The best solution I found was to create bar report based off the INC table and then add a dataset for the ar_incident table.