Can I search a specific document in sql and see all the keywords

[Jose_Contreras1]

Hello All,

I wanted to know which table can I query to see a specific document type and view all the keywords on a document in SQL.

For example,  I would like to find a document under document type "invoices".  I want to find a specific document under "invoices" using the "invoice#" keyword and see all the keywords associated with that specific document.  I need to do this inside of SQL to verify that there are no duplicate keywords.

Reason for this is that Onbase does not show duplicate keywords but our portal query returns 2 different values.  For example, Onbase may show invoice# =A  but the portal returns invoice# =A and Invoice# =B.  I want to verify if the duplicate results fall under the portal code or Onbase API returning values.

Thanks,

Jose

[Ansley_Ingram · Accepted solution]

Hi Jose,

The Database Reporting Guide provides information on the schema related to documents and keywords and should provide sufficient guidance for someone with SQL experience to generate a query similar to what you are requesting.

Ansley

[Jose_Contreras1]

My experience with SQL is limited but I know enough to only do select statements.  This is a great document and should be able to figure something out.  Thank You!

[Barbara_Ebel-La]

Here are some basic ones to get you started:

-- find all keyword numbers and names
SELECT * FROM hsi.KEYTYPETABLE order by 1;

-- find all doctype numbers and names
select itemtypenum, itemtypename from hsi.doctype order by 1;      

-- Find all keyword types assigned to a doctype
SELECT ixk.keytypenum, trim(ktt.keytype), ixk.itemtypenum, trim(dt.itemtypename)
FROM HSI.ITEMTYPEXKEYWORD ixk
LEFT OUTER JOIN hsi.doctype dt ON dt.itemtypenum = ixk.itemtypenum
LEFT OUTER JOIN hsi.keytypetable ktt ON ktt.keytypenum = ixk.keytypenum
WHERE ixk.itemtypenum = 378 -- doctype number
ORDER BY 2;

Once you find what keyword types are associated with a doctype, you can join those tables to the hsi.itemdata table to the relevant keyitem table.  For example:

select i.itemnum, k544.keyvaluechar

from hsi.itemdata I

join hsi.keyitem544 k544 on k544.itemnum = i.itemnum

where i.itemnum = '12345678'

;

[Naveen_Bhat1]

In the below query, ISSNK being the name of the keyword. You will have to add a left outer join for all as shown below. You should know the keyword numbers and also the keywords are single table and not dual tables. 

select ISSNK.keyvaluebig [Insured SSN], IPK.keyvaluebig [Insured phone]
from hsi.itemdata Item inner join hsi.doctype DT on Item.itemtypenum = dt.itemtypenum
left outer join hsi.keyitem1603 ISSNK on item.itemnum = ISSNK.itemnum
left outer join hsi.keyitem1572 IPK on item.itemnum = IPK.itemnum
where dt.itemtypename = 'New Business'

If Dual tables then you will have to add Keyxitem and keytable.

left outer join hsi.keyxitem1603 ISSNK1 on item.itemnum = ISSNK1.itemnum

left outer join hsi.keytable1603 ISSNK2 on ISSNK1.itemnum = ISSNK2.itemnum