question How to write a "where clause" in a database view in ITSM forum

How to write a "where clause" in a database view

vishnumaaya — Thu, 27 Feb 2025 12:57:52 GMT

Hi,

1. After creating a database view , adding the tables you want to combine , how to define where clause?

2. How do we know which one to write where clause for??

3. For example, see the picture below,

- How to judge where to write the Where clause? as in where clause is written near the change_request and not task_sla

- Why was it written in change request where clause?

- Why did it not use change_request_sla table n used task_sla table??

Please share any article, blog or videos if you have.

Thanks,

Vaishnavi

Re: How to write a "where clause" in a database view

Ankur Bawiskar — Thu, 27 Feb 2025 13:35:47 GMT

@vishnumaaya

Explanation here:

1) row 1 is for change_request, it should be with 100 order

2) row 2 is for task_sla, it should have where clause and should be with 200 order

You can write where clause in any row but ensure it's correct with correct prefix

- Why did it not use change_request_sla table n used task_sla table?? -> this might be as per customer requirement as SLAs are stored in task_sla table

If my response helped please mark it correct and close the thread so that it benefits future readers.

Re: How to write a "where clause" in a database view

Dr Atul G- LNG — Thu, 27 Feb 2025 13:38:43 GMT

Hi @vishnumaaya

- How to judge where to write the Where clause? as in where clause is written near the change_request and not task_sla

Atul: it is mainly on main table like where is main record exists.

- Why did it not use change_request_sla table n used task_sla table??

Atul: The Sla stored in task_sla table and i.e. reason we use task_sla.

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0719186

Re: How to write a "where clause" in a database view

Community Alums — Thu, 27 Feb 2025 13:41:29 GMT

Hi @vishnumaaya ,

Refer to this example :

https://www.servicenow.com/community/training-and-certifications/database-view-quot-where-clause-quot-question/m-p/2954031

https://www.servicenow.com/community/itsm-forum/database-view-where-clause/m-p/2474805

Re: How to write a "where clause" in a database view

Ankur Bawiskar — Thu, 27 Feb 2025 14:50:17 GMT

@vishnumaaya

Thank you for marking my response as helpful.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Re: How to write a "where clause" in a database view

Community Alums — Fri, 28 Feb 2025 02:17:28 GMT

Hi @vishnumaaya ,

Re: How to write a "where clause" in a database view

Viraj Hudlikar — Fri, 28 Feb 2025 02:43:27 GMT

Hello @vishnumaaya

Please refer below thread & videos:
Solved: Database View - ServiceNow Community

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0719186

https://www.servicenow.com/docs/csh?topicname=c_DatabaseViews.html&version=latest

https://youtu.be/_JY462TmXxU?si=2OvPCBW_Ecid4flE

https://youtu.be/vk1MYBxTSxY?si=j-Y1f0BFCdknobJT

https://youtu.be/vJo3BG2RCVc?si=Apuspc_SFU12gBbD

If my response has helped you hit helpful button and if your concern is solved do mark my response as correct.

Thanks & Regards
Viraj Hudlikar.

Re: How to write a "where clause" in a database view

vishnumaaya — Tue, 04 Mar 2025 09:37:26 GMT

Hi ,

This is not created by the client but OOB one, I just selected this as an example only!

why is order important in database view?

Thanks,

Vaishnavi

Re: How to write a "where clause" in a database view

vishnumaaya — Tue, 04 Mar 2025 09:40:23 GMT

Hi,

I have multiple tables added,

incident_metric, cmdb_ci_outage and u_business_unit (custom table which has markets details).

I would want Incident number column, Outage number column, Duration(Outage) column and business unit column (where the outage has occurred) . How can I write database view for this?

Thanks,

Vaishnavi

Re: How to write a "where clause" in a database view

Dr Atul G- LNG — Tue, 04 Mar 2025 13:39:51 GMT

Hi @vishnumaaya

So on this case you need to find out what is common in table and then create the where clause to get the results, You can look OOTB database view to get an idea.