Where Clause in Database View

[Kiff]

Hello All,

I have a field "major incident state" present on two tables (not sure if this affects anything but the Element name on Incident table for this field is "major_incident_state" while on the custom table its "u_major_incident_state".

Need help defining the where clause in the database view.

Variable prefix for incident table is "incident"

Variable prefix for custom table is "er"

What will the Where Clause look like in this case?

Thanks.

[Astrid Sapphir1]

Hi,

This will depend on what you want to show and your logic but I'll give an example. Assuming you want to show Expense Reporting records (with a reference field for Incidents) with a Major Incident state of 'accepted', and that you have logic to set the u_major_incident_state field on ER:

Expense Reporting is View Table with order 100 and no Where clause.

Incident is View Table with order 200 and following Where clause:

incident_sys_id = er_u_incident && (er_u_major_incident_state = 'accepted')

The above is an example only. It is hard to give you more guidance without a better understanding of what your logic and requirement is - however, I'll outline the basic framework.

• Your lowest order view table will not have a where clause
• Your next table(s) use where clauses to define what you want to see
• Your where clause generally references the current view table and what it matches on the preceding table - such as where your incident is in a reference field and a certain value should be checked.

Please let me know if this was helpful or if you need any further guidance. This sort of thing is best learnt through practice.

Kind Regards,

Astrid

[Kiff]

Thanks Astrid.

I think this might be as a result of either not being totally sure of how to pull a report using Database view or it could be the fact that i am not referencing the right field on both tables because nothing seems to be pulling up in my report.

[Astrid Sapphir1]

Hi Kiff,

A database view can be thought of as a virtual table - as long as you are addressing the right information in your condition (if any) then it should work as reporting on other tables.

Without knowing the schema I cannot tell you for sure where the issue is occurring. If you have a reference field for an Incident record on the Expense Reporting table, you should try something like the below and then do further restriction with your report. If you must restrict it in the Where clause, the excerpt in my first comment should have the correct syntax.

incident_sys_id = er_u_incident

I also neglected to mention that if you are restricting fields in the view by using View Fields, then you must have a View Field set for the fields above. This is because having any View Field means it will only query for the View Fields on that View Table. If you don't have a View Field for your incident reference on ER for example, it can't find that field and so joining the information will fail.

Using the simplified syntax and checking the View Fields element should help you make data visible and refine from there.