Trouble Building Database View – Unexpected Results with Campus Data

[lonesoac01]

Hi everyone,

I'm working on building a Database View that should return a single row per incident, including the INC number and its related campus data points. However, the results I'm getting are not accurate — instead of one consolidated row per incident, I'm seeing unexpected or duplicated data.

Here’s the WHERE clause I’m currently using in the incident table record:

inc.caller_id.location.campus.sys_id = campus.sys_id

The tables involved are:

• incident
• sn_wsd_core_campus

My goal is to join the incident with its associated campus information based on the caller's location. If anyone has suggestions on how to refine the join or troubleshoot why the results aren't as expected, I’d appreciate the help!

Thanks in advance.

[Brad Bowman]

The purpose of a Database View is to join one or more tables together so that you can see data from all tables.  If you can view the desired data columns in a list view or report by dot-walking (from incident, caller_id, location... in this case) then you don't need/want a Database View.  A Database View will ALWAYS inherently show one row for each record on all joined tables.  Are all of the addresses and or site codes shown in your screen shot valid campuses on the incident caller's location record?  

https://www.servicenow.com/docs/bundle/zurich-platform-administration/page/use/reporting/concept/c_D... 

[Nawal Singh]

Hi @lonesoac01 ,

Please refer to the below link and I believe dot walk is creating an issue here-

https://www.servicenow.com/docs/bundle/washingtondc-application-development/page/use/reporting/task/...

Or Review the below Screenshot for reference and test accordingly- 

If it's help you to resolve the issue please mark helpful or accept the solution

Thank you

Nawal Singh