Database view

[priyanka1028]

Hi Team,

I want to show the RITMs which are in open, pending and work-in-progress state but their related SC Tasks and Change Tasks both are closed using database view.

Please help me with this requirement.

[Kieran Anson]

It's documented here Add a table to the database view (servicenow.com) - 

To add an OR to your Where clause, use ||.
For example, to query all incidents related to RFCs OR all incidents that are the parent of a change request, use the syntax: inc_rfc = chg_sys_id || chg_parent = inc_sys_id.

[ravjs]

You can do multiple condition using &&.
For example, 'screq_active = 1 && another_condition = true'
You can also add additional condition on the where clause of each table you join.

[Pavankumar_1]

Hi @priyanka1028 ,

1.  You can refer below picture and create database as below and add your conditions.

2. where clause on sc_task table record (sctask_request_item=ritm_sys_id) && (ritm_state='1' || ritm_state='2') && (sctask_state='3') . Refer below

Info: state 1 is for open , 2 is for work in progress, 3 is for closed complete

[Murthy Ch]

Hello @priyanka1028 

As mentioned by @Pavankumar_1 and @Kieran Anson 

You can create the database view for your scenario.

In addition to it if you want to add the where clause for change_tasks as well:
you just need to create one more record in View tables related list with the below condition:

(changetask_parent=ritm_sys_id)&&(changetask_state='3')&&(ritm_state='1' || ritm_state ='2')