Database view and Dot walking

[JJ1]

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 ?

[sergiu_panaite]

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,

[JJ1]

Hi Sergiu,

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

Thanks,

Jeevan

[sergiu_panaite]

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,

[mathiasjohansso]

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?