In ServiceNow scripting (global scope, Xanadu), is there an API that compiles to a real DB-side correlated NOT EXISTS / LEFT JOIN … IS NULL against the same table — without the inner subquery's result set
The SQL shape I'm trying to replicate (column names abstracted):
SELECT COUNT(DISTINCT t.key_a) AS result_count
FROM my_table t
WHERE (t.category = 'A' OR t.category IS NULL OR t.category = '')
AND NOT EXISTS (
SELECT 1
FROM my_table t2
WHERE t2.key_a = t.key_b
AND t2.category IS NOT NULL
AND t2.category <> ''
AND t2.category <> 'A'
)
AND NOT EXISTS (
SELECT 1
FROM my_table t2
WHERE t2.key_b = t.key_a
AND t2.key_b IS NOT NULL
AND t2.category IS NOT NULL
AND t2.category <> ''
AND t2.category <> 'A'
);
Two NOT EXISTS clauses against the same table with opposite cross-field matches (t2.key_a = t.key_b in the first, t2.key_b = t.key_a in the second). Runs daily as a scheduled job in system context (no MAINT
role). The inner subquery's result set can be millions of rows — too large to bring into script memory.
Any help would be greatly appreciated. Thanks!
