How to join sys_history_line table with incident table

[SSA1]

Hi,

I'm having a issue, when i create database view using sys_history_line and incident. TThis is how I tried. Please correct me if I'm doing it wrong. I have tried several methods. It didn't work.

[Ankur Bawiskar]

Hi,

Actually sys_history_line is present in Table Rotation so should not be used in database views.

set field refers to sys_history_set which has the record sys_id in id field

so you will have to include sys_history_set table as well

also remember id field on sys_history_set is document_id type; so you need to include the table as well in condition

something like this

Table                   Order    Variable prefix     Where clause
sys_history_line     100       lin         
sys_history_set      200      set                   lin.set=set.sys_id
incident                300      inc                   set_id=inc_sys_id && set_table=incident

Regards
Ankur

[SSA1]

Its not working. It says no records to display. and also set word cannot be used as a prefix since it is a reserved word.

[Ankur Bawiskar]

Hi,

change the prefix and share your database view where clause screenshots

regards
Ankur

[Prateek07]

Hi @Ankur Bawiskar,

I have a similar scenario but i need to connect hr case table and history table.

I am using below criteria, It is not working, it is giving blank data. Please suggest.

Does dot walking wok in DB where clause?