Database view "where clause" question

[Lee Kraus2]

Hello Community,

I just learned about Database Views and I'm hoping it can help me with a reporting situation my Service Desk is asking for. I need a report to show Incident tickets with an updated work notes field.

For my Database view, I named it u_database_view_test, and on the Related Links box for View Tables I have the Incident table (order 100) and the sys_journal_field table (order 200). Not sure if the order matters. Incident table variable prefix is "inc" and the sys_journal_field variable prefix is "journal".

What I'm not sure about is if my where clause is actually giving me what I'm looking for. I have "inc_sys_id = journal.element_id" and while the table that generates has a Work notes column, I'm not sure I'm getting what I need. Where can I go to find good "where clause" examples. I don't know how to specify in my clause I only want the Work Notes from the sys_journal_field table, that way I can filter by when the field was updated on an incident.

The reason I need this table to also show incidents is so that the Service Desk managers can click on any of the INCXXX numbers to open any ticket they are auditing while looking at the report. Also, I need the Updated column to pull the date from the sys_journal_field table, and not the Incident table as well as Updated by. This is why I'm not sure if the order of the tables matters. does the first table listed become the foundation for the database view and the other sys_journal_field information is just added in other viewable columns?

I've attached a screenshot so you can see what I have set up. Thank you in advance for the help!

[JP - Kyndryl · Accepted solution]

Hi Lee,  

Your DB View seems fine.

I just successfully tested a similar one:

and got this result:

to get only the fields you want,  you may click on the Tables on the left and for each of them, pick the fields you would like to be in the DB View.

So the sys_id and the number for Incident.

and the element_id, Value, created (journal) and created by (journal)  for the sys_journal_field table.

The fields you pick need to include the ones in the where clause.

[Lee Kraus2]

Thank you for the help!

[Brian Lancaster]

The first thing you should do is remove the "." in your where clause. I'm surprised it did not throw an error. It should be journal_element_id.

Also you in a report you can add work notes field to the columns to display the work notes. 

[Lee Kraus2]

the quotes were to separate my clause from my sentence it was in.