I suspect this has been asked on the Community before, but trying to find a good explanation is a challenge.
I have the following in a Database view:
The view loads, but it doesn't limit alm_hardware records to model_category of Computer (hdw_model_category='81feb9c137101000deeabfc8bcbe5dc4'). However, if I add a before query business rule and add that condition, it (unsurprisingly) works fine:
(function executeRule(current, previous /*null when async*/) {
current.addEncodedQuery('hdw_model_category=81feb9c137101000deeabfc8bcbe5dc4');
})(current, previous);
Why doesn't that part of the where clause in the database view have any effect?
Thanks!
Hello @chuckn,
I recreated your database view and had the same issue.
According to a ServiceNow article the "'Where clause in a database view is primarily used to specify join conditions. It cannot be used to filter based on field values (ex. 'Active' = 'true')." ((Filtering using the 'Where clause' in database views))
However, ServiceNow is using this typeof "filtering" in some of their own out of the box database views.
From what I understand and found out with try and error is that it seems to work for right joins, but not for left joins. I guess the left join basically overwrites the "=" condition. The idea of the left join is to join all entries from the first table (on the left), so after filtering them out they will be added again by the left join.
In the following setup of the database view the "=" filter condition is working:
Not sure if this will still fulfill your requirement.
I hope this helped!
Greetings,
Sören
Hello @chuckn,
I recreated your database view and had the same issue.
According to a ServiceNow article the "'Where clause in a database view is primarily used to specify join conditions. It cannot be used to filter based on field values (ex. 'Active' = 'true')." ((Filtering using the 'Where clause' in database views))
However, ServiceNow is using this typeof "filtering" in some of their own out of the box database views.
From what I understand and found out with try and error is that it seems to work for right joins, but not for left joins. I guess the left join basically overwrites the "=" condition. The idea of the left join is to join all entries from the first table (on the left), so after filtering them out they will be added again by the left join.
In the following setup of the database view the "=" filter condition is working:
Not sure if this will still fulfill your requirement.
I hope this helped!
Greetings,
Sören
