How to add filters in the Database View

Paul125 · ‎03-02-2018

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!

Paul125 · ‎03-05-2018

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.

View solution in original post

gputker · ‎06-13-2019

I got this working on a choice (TRUE/FALSE) field:

(cirel_child=eaapps_sys_id || cirel_parent=eaapps_sys_id) && eaapps_u_critical_application=1

The text in black joins two of the same tables with an OR condition. The text in red returns only TRUE values for the critical application field.

Took a lot of tries - I'm rapt.