SQL Tables

[Ragini_Bhondve]

I have to generate report which has the following Column :

• Column Names in the report:
  1. Document Type Name
  2. Keyword Name with at least one document having more than one value
  3. Number of documents with more than one value for that keyword.

• Keywords run on  two many  tables how to find out them? Can anyone please guide me?
  Thanks in advanced

[Austin_Barber1]

@Ragini Bhondve - What I usually do is turn on the db profile in the diagnostics console in one of our development/test environments to show what queries are being ran when looking up documents in OnBase. https://support.hyland.com/r/OnBase/Diagnostics-Service-and-Diagnostics-Console/Foundation-23.1/Diag...
That might help point you towards your keyword tables. In OnBase all keywords have a keytype number. That number usually corresponds to a table name like hsi.keyitem79 in the database with the keyword you are looking for. Then for example, you can join the itemnum in the hsi.keyitem79 to the hsi.itemdata table. 

Reporting Dashboards does have an OOB document query that you could try and see if that would fit your needs before writing a custom sql query. I would think that it should give you a baseline of what your asking. Only thing I'm not sure on would be how you could count the number of documents with more than one value for that keyword. That isn't something I have tried yet. 

Does that help point you in the right direction? Let me know if you have any other questions. 

[Ragini_Bhondve]

If there are 150 keyword then how can I search keyword table for each keyword & find out the more keyword values.

[Ragini_Bhondve]

I have to do this for each table there is any short cut for this

select dt.itemtypename, kt1.itemnum [Doc Handle], kt11.keyvaluechar [Keyword Value]
from hsi.keyxitem1 kt1
inner join hsi.keytable1 kt11 on kt11.keywordnum = kt1.keywordnum
inner join hsi.itemdata item on item.itemnum = kt1.itemnum
inner join hsi.doctype dt on dt.itemtypenum = item.itemtypenum
group by dt.itemtypename , kt1.itemnum , kt11.keyvaluechar having count(kt1.itemnum) > 1
union
select dt.itemtypename, kt2.itemnum [Doc Handle], kt22.keyvaluechar [Keyword Value]
from hsi.keyxitem1 kt2
inner join hsi.keytable1 kt22 on kt22.keywordnum = kt2.keywordnum
inner join hsi.itemdata item on item.itemnum = kt2.itemnum
inner join hsi.doctype dt on dt.itemtypenum = item.itemtypenum
group by dt.itemtypename , kt2.itemnum , kt22.keyvaluechar having count(kt2.itemnum) > 1
union
select dt.itemtypename, item.itemnum [Doc Handle], k126.keyvaluechar [Keyword Value]
from hsi.keyitem126 k126
inner join hsi.itemdata item on item.itemnum = k126.itemnum
inner join hsi.doctype dt on dt.itemtypenum = item.itemtypenum
group by dt.itemtypename ,item.itemnum , keyvaluechar having count(k126.itemnum) > 1

[Austin_Barber1]

@Ragini Bhondve - Sorry for the late reply. I don't know of a better way to recursively go through all document types if that's what you are after. 

Normally, I do something like this when running a report on keywords on a document type. However, I'm usually limiting my query to a specific document type group or document type. The below is just for a single document type where documents are active (not deleted). Then you could add in your having clause in there. Not sure if that helps simply your query above or not.  

SELECT TOP(2001)

    itd.itemnum,

    RTRIM(t153.keyvaluechar) ID,

    RTRIM(ISNULL(t151.keyvaluechar,'')) LastName,

    RTRIM(ISNULL(t150.keyvaluechar ,'')) FirstName,

    RTRIM(ISNULL(i363.keyvaluechar,'')) SA_DocType

FROM hsi.itemdata itd

    LEFT JOIN hsi.keyxitem153 x153 ON itd.itemnum = x153.itemnum

    LEFT JOIN hsi.keytable153 t153 ON x153.keywordnum = t153.keywordnum

    LEFT JOIN hsi.keyxitem151 x151 ON itd.itemnum = x151.itemnum

    LEFT JOIN hsi.keytable151 t151 ON x151.keywordnum = t151.keywordnum

    LEFT JOIN hsi.keyxitem150 x150 ON itd.itemnum = x150.itemnum

    LEFT JOIN hsi.keytable150 t150 ON x150.keywordnum = t150.keywordnum

    LEFT JOIN hsi.keyitem363 i363 ON itd.itemnum = i363.itemnum

WHERE (itd.itemtypenum = 741) AND ((itd.status = 0))

ORDER BY i363.keyvaluechar