Adam has a good solution to this problem that i have used previously.
It is about half way down this post and involves a custom query.
Original Post:
Adam Shane:
"
I realize that there have been a number of replies, but I thought that I would weigh in a bit and offer some clarification. In the past our recommendation is to create a SQL Custom Query to narrow down the NULL values for the particular keyword. That being said, there are some nuances with creating the SQL.
For instance, lets say that you were looking for a value of a dual table keyword, the SQL would like like the following:
-- XXX is the Document Type Group num
-- YYY is the dual table keyword num
SELECT *
FROM hsi.itemdata
LEFT OUTER JOIN hsi.keyxitemYYY k0 ON hsi.itemdata.itemnum = k0.itemnum
WHERE k0.itemnum IS NULL
AND hsi.itemdata.itemtypenum IN (XXX)
AND hsi.itemdata.status + 0 = 0
In order to turn this query into a SQL Custom Query in config it would need to look like the following (in this example the keyword num is 1 and document type is 101):
For a single table keyword, the SQL would look like the following:
-- XXX is the Document Type Group num
-- YYY is the single table keyword num
SELECT *
FROM hsi.itemdata
LEFT OUTER JOIN hsi.keyitemYYY k0 ON hsi.itemdata.itemnum = k0.itemnum
WHERE k0.itemnum IS NULL
AND hsi.itemdata.itemtypenum IN (XXX)
AND hsi.itemdata.status + 0 = 0
In order to turn this query into a SQL Custom Query in config it would need to look like the following (in this example the keyword num is 120 and document type is 101):
Finally, I posted some information about caveats that you can run into when creating SQL Custom Queries here.
"
