How to create Database view with condition?

[guy]

Hi,

I'm trying to create a table view that displays only the approvals with status in "requested". Somehow the table shows all approvals without a filter. Below is attached the view created and conditions.

Thanks,

Guy.

[sergiu_panaite]

A database view usually implies a JOIN between 2 or more tables. If you only want records from a table based on a condition then you can use a report for that.

The "Where clause" in the database view is not same as a SQL WHERE clause, it's just the condition from the JOIN. Here is an example:

- a simple SELECT with a WHERE clause:

SELECT * FROM persons WHERE firstname='John';

- a JOIN between 2 tables:

SELECT * FROM incident INNER JOIN task ON incident.sys_id = task.sys_id WHERE incident.incident_state=2;

The "Where clause" from database view is actually the highlighted part above (ON incident.sys_id = task.sys_id).

You cannot add a WHERE clause in a database view same as a standard WHERE clause in SQL.

Regards,

Sergiu

[matthew_magee1]

I haven't had the opportunity to do this in a live situation yet, but I figured it would be cool to check out.

It looks like a Database View is used to combine 2 or more tables. Since you have 1 table (sys approver), I'm guessing the where clause might be irrelevant.

Why not just create a module link that points to a query like:

<instance_name>/sysapproval_approver_list.do?sysparm_query=state%3Drequested

When the user clicks on the module...whola, all they see are the approvals in a requested state

[Chandresh]

I have created one basic database view to get REQ and RITM in one list below is the snapshot of that

Once you click on try it it will give you the list fo RITMs corresponding to the REQ in a list form.

Also, below link will give you a fair idea about Database views.

Database Views - ServiceNow Wiki

[guy]

Thanks