Database View Shows No Results When I Add Class to the Where Clause

[Robert Campbell]

I created a view that was working but timing out. I am trying to edit the view so it doesn't have as much data so I want to ignore when child class is Manual Endpoint (for now but more to come) but when I add that clause, I get no results.

If I remove " && r2_child_sys_class_name != 'cmdb_ci_endpoint_manual'" the view "works" but it times out so I'm trying to remove some useless to us data.

[Ankur Bawiskar]

@Robert Campbell 

it's definitely due to huge data that it's timing out and the where clause is taking long time to join those tables

Try to add additional condition to minimize the records

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

[Juhi Poddar]

Hello @Robert Campbell 

In a database view, dot-walking is done using underscores (_) instead of dots (.).

So, for example:

ba1.operational_status != 'Retired'

should be replaced with:

ba1_operational_status != 'Retired'

Similarly, update the Where clause for all other tables accordingly.
For more details please refer to: Add a table to the database view

Hope this helps!

"If you found my answer helpful, please like and mark it as an "accepted solution". It helps future readers to locate the solution easily and supports the community!"

Thank You
Juhi Poddar

[Robert Campbell]

Everything works except when I add that one where clause. As long as it's only 1 dot-walk, I can use the '.' rather than '_'.

[Juhi Poddar]

Hello @Robert Campbell 

I tried replicating this in my PDI and interestingly found that r2_child refers to the cmdb_ci_service, which is represented by bs1 in this case. So, you can replace:

&& r2_child_sys_class_name != 'cmdb_ci_endpoint_manual'

with:

&& bs1_sys_class_name != 'cmdb_ci_endpoint_manual'

Make sure to apply this condition specifically for the cmdb_ci_service table.

Hope this helps!

"If you found my answer helpful, please like and mark it as an "accepted solution". It helps future readers to locate the solution easily and supports the community!"

Thank You
Juhi Poddar