Database view - records created today in where clause

[tonynys]

I try to filter records created today, mixing in mysql functions in the where clause of the table

ct.sys_created_on > DATE_ADD(NOW(),INTERVAL -1 DAY) and ct.sys_created_on < DATE_ADD(NOW(),INTERVAL 1 DAY)

For some reason EMPTY created dates are returned and the join with my sys_user_group query where condition is not happening anymore

[sergiu_panaite]

Hi Tony,

The "where clause" you see in a database view in instance UI refers to a join condition, and it's not the real SQL WHERE clause. Currently you cannot add a real SQL WHERE clause in a database view (but you can filter a database view if you add it into a report).

An example will probably clarify what I mentioned above:

- example on MySQL on the join condition:

SELECT t1.name, t2.salary

  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

- example on MySQL on the join condition plus a WHERE clause:

SELECT left_tbl.*

  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id

  WHERE right_tbl.id IS NULL;

Hope this helps.

Regards,
Sergiu

[smcdonaldaz]

The where clause in a DB View is only used to join 2 tables. You can however create a before query business rule on the view table. See the knowledge article below.

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0719186