How to add filters in the Database View

[Paul125]

Hi,

I need to create a database view that will show all the applications, servers with middleware relationships. For this exercise I've added some views on the respective tables to pull the applications, middlewares, servers. But here comes an issue, these Where clauses bringing all applications that are retired and non-retired and so on..
I would want to add some filters to the where clause that will query operational status!=retired, Consist::IspartOf relationships should be middlewares and so on..Can someone help me with adding these kind of filters to the where clause. Or else should I be using business rules on database view? Thanks!

[sethivarun]

Try using the steps provided in following link 

Use disjunctions in complex queries

[Paul125]

Thanks, I am gonna try that.

[Paul125]

This link doesn't help. I am looking for an example.

[Paul125 · Accepted solution]

I found the solution for filtering out using sys_choice tables. Process follows as shown below.

1. Add sys_choice table to first row. Then give the prefix as desired.

2. Check for the target field on application(system_status or operational_status or install_status and so on) table form and look the same thing on sys_choice table and grab the details from the desired record and add it in the first line at Where clause as follows.

ch_name = 'cmdb_ci' && ch_element='operational_status'&&ch_value=6

3. Add this clause in the 3rd line to check whether the application is retired or not.

(application_operational_status!=ch_value)&&application_sys_id= rel1_parent

Same way do it for different scenarios on different tables.

Thanks.