Database View Table Where Clause to contain NULL

[mike_bush]

I want to improve my DB View performance by including a null test in a where clause (rather than let hundreds more records through and have to include the condition in every report I write).

Syntax for where clause doesn't SEEM to support this - neither

        field != ''

nor

        field = NULL

seem to work?

Has anyone managed to get a null test into a View Table Where Clause?

[tony_barratt · Accepted solution]

Hi Mike,

If anyone has found a way to work a null test into a database view where clause, I do not see this   mentioned in the ServiceNow Community.

Adding a report on the db view has been mentioned as a workaround but you are already doing that.

The possibility of using a before query business rule is mentioned here:

http://www.snc-blog.com/2014/08/07/a-view-on-database-views/

I have not tried this, and it might or might not improve performance.

Best Regards

Tony

[mike_bush]

Thanks Tony, you aimed me in precisely the right direction.

I attach a BEFORE QUERY DB RULE to the database view 'table'.

This scripts "current.addNullQuery('field');

et voila!

[tahnalos]

Hi.

I don't mean to resurrect a dead thread, but wondering if the business rule (being applied to the table itself) would mean all of these records where field = NULL would mean that these records won't show up in a normal search?

Thanks

[mike_bush]

BR only fires on the Database View