Custom Query SQL - Docs with a NULL kw

[Henry_Boardman2]

I've never done a SQL custom query before and I'm hoping someone has an example of what I'm looking for since it seems like it would be handy for most people.

I need to get a list of docs that have a certain KW attached but where that KW is empty.

If anyone has any examples or even stubs of a query I'd greatly appreciate it. For the record, it would have been nice to include an example of a simple customized custom sql query in the MRG.

[Larissa_Armand]

Hi Henry, 

From page 150 of the EP3 Database Reporting Guide (https://community.hyland.com/gallery/items/73715-database-reporting-guide-onbase-foundation-ep3-refe...😞 

Query to list all documents for Document Type # 101 that do not have an ACCT# (also displays First Name and Last Name; for SQL Server only):

select i.itemname, kt1.keyvaluechar, kt2.keyvaluechar
from hsi.keytable101 kt1, hsi.keytable102 kt2, hsi.keyxitem101 kx1,
hsi.keyxitem102 kx2, hsi.itemdata i
left outer join hsi.keyxitem105 kx5 on kx5.itemnum=i.itemnum
where i.itemtypenum=101
and i.itemnum=kx1.itemnum
and i.itemnum=kx2.itemnum
and kx1.keywordnum=kt1.keywordnum
and kx2.keywordnum=kt2.keywordnum
and kx5.itemnum is null

You'll need to know the document type number(s) and keyword type number (which you can get in Configuration or by querying the doctype or keytypetable tables). 

[Henry_Boardman2]

Thanks, that's the start I was looking for. I'm not using dual table keywords so I think the query can be simpler than using dual-table kws. However, I need to take it a step further.

I can get the doc types that use a certain keyword (102).

-- All docs that use 102 - EX ID#
select distinct d.itemtypenum
from hsi.itemdata id
,hsi.itemtypexkeyword x
,hsi.doctype d
where x.itemtypenum= id.itemtypenum
and x.keytypenum = 102
and id.itemtypenum = d.itemtypenum

But now I want to get all docs in that list of itemtypes where hsi.keyitem102.keyvaluechar is null or empty.

Is that too much to shoehorn in to a custom query?