Change Request/Change Task Database view

[Jon_Jennings]

I need to report on Change Requests opened in a given date range and on their related Change Tasks (if any).

I've created a database view joining the change_request and change_task tables.   My where condition is as follows:

change_request       prefix = cr       where clause = EMPTY     left join = false

change_task                 prefix = ct         where clause = ct_change_request = cr_sys_id       left join = false

My understanding is that this should yield all Change Requests and any Change Tasks whose "Change Request" field value matches a Change Request sys_id.

What I get instead is only Change Requests that have Change Tasks related as well as those Change Tasks.   We often have Change Requests with no Change Tasks related (yet) and need to see those in the report as well.   For some reason this is eluding me. Any help would be greatly appreciated.

[Chuck Tomasi]

Hi Jonathan, it's been a LOONG time since I've done database views... If I recall, to get what you want, doesn't one of those two have to have left join = true?

Reference:

Database Views - ServiceNow Wiki

[Jon_Jennings]

Hi Chuck,

My understanding is that there only needs to be a left join if you don't want to constrain on the field(s) in your where clause.   However, hedging against a misunderstanding on my part, I have previously tried left joins in each of the two tables separately as well as left joins in both at the same time and none of those combinations were successful.

FWIW - I used this article as a refresher:

ServiceNow Admin 101: Observations on Database Views, Part II

[Chuck Tomasi]

You are now officially ahead of me on (limited) database view knowledge.

[Rashmi Bansal]

Hi Chuck,

Please help in creating database view of task table with incident, problem and change_request table. so that with one view we can see all the fields of incident, problem and change