question Re: Database view and Dot walking in Developer forum

Database view and Dot walking

JJ1 — Tue, 05 Apr 2016 00:26:23 GMT

Hi All,

I am trying to create a view between 2 tables - cmdb_ ci_computer and sc_task where the condition should be requested_for (sc_task/Req) = cmdb_ci computer_u_user (custom field which refers to user form)

What should be the database view criteria ?

I created a view with view tables sc_task and cmdb_ci_computer

ci prefix for CI computer table

stsk prefix for sc_task table

Where clause on cmdb_ci_computer is " ci_u_user = stsk_request_item.request.requested_for" but it looks like dot walking does not work ?

Re: Database view and Dot walking

sergiu_panaite — Tue, 05 Apr 2016 07:28:47 GMT

Hi Jeevan,

The "where clause" you see in database views in ServiceNow is actually a "ON" clause from a JOIN:

SQL Joins

We don't support dot walking on ON clause. There is no real SQL WHERE clause in these database views.

Hope this helps.

Regards,

Re: Database view and Dot walking

JJ1 — Tue, 05 Apr 2016 13:43:57 GMT

Hi Sergiu,

Thanks for the clarification ,so there is no way to achieve the above requirement ?

Thanks,

Jeevan

Re: Database view and Dot walking

sergiu_panaite — Tue, 05 Apr 2016 13:48:08 GMT

Hi Jeevan,

It might work but you need to add 2 more tables in the join/ database view, one for table of request and another one for table of requested_for and use something like:

Where clause

ci_u_user = stsk_request_item

stsk_request_item = xxx_request

xxx_request = yyy_requested_for

You'll need to test it this.

Regards,

Re: Database view and Dot walking

JJ1 — Tue, 05 Apr 2016 17:58:29 GMT

Thanks Sergiu,

I will try this option.

Re: Database view and Dot walking

mathiasjohansso — Fri, 18 Nov 2016 09:42:04 GMT

Hi!

I tried this but it seems to require all tables to contain data related to each other.

E.g, I have created a database view to display Problem data and incident data based on Incident related to problem. I also want to display the Country where these incidents were registered.

The database view looks like

View tables:

problem

where: prb_sys_id=inc_problem_id

incident

where: inc_problem_id=prb_sys_id

cmn_location

where: (null)

This only displays one record, since there is only one record where the location on the incident is set and contains country data.

If i remove the cmn_location, the list will display 29 records..

I want to display all the 29 records WITH the country if applicable.

Am I doing wrong?

Re: Database view and Dot walking

telmo — Fri, 18 Nov 2016 09:48:38 GMT

Hi Mathias,

If not all incidents have the location set, then set the "Left join" flag when you define the "cmn_location" within the Database View.

Telmo

Re: Database view and Dot walking

mathiasjohansso — Fri, 18 Nov 2016 10:01:38 GMT

This worked great! Thank you!

Re: Database view and Dot walking

vidyasnpractice — Thu, 01 Oct 2020 19:59:40 GMT

above solution worked. thank you