Keytypenum | Keytype | Additional Information |
---|---|---|
101 | Last Name | Dual table alpha: hsi.keytable101, hsi.keyxitem101 |
102 | First Name | Dual table alpha: hsi.keytable102, hsi.keyxitem102 |
105 | Acct. # | Dual table alpha: hsi.keytable105, hsi.keyxitem105 |
129 | WF Task Date | This is a date, single table: hsi.keyitem129 |
Query to verify the internal Document Type unique ID for 3 Document Types:
select * from hsi.doctype where itemtypenum in (101,102,103)
Query to list all documents (displays Auto-Name string only) for Document Type # 101:
select itemname from hsi.itemdata where itemtypenum=101Query to list all documents for Document Type # 103 with an October Document Date:
select itemname from hsi.itemdata
where itemtypenum=103
and itemdate between '20081001' and '20081031'
Query to list all documents for Document Type # 103 with a November Document Date (also display First Name and Last Name Keyword values):
select i.itemname, kt1.keyvaluechar, kt2.keyvaluechar from hsi.itemdata i, hsi.keytable101 kt1, hsi.keytable102 kt2, hsi.keyxitem101 kx1, hsi.keyxitem102 kx2 where i.itemtypenum=103 and i.itemnum=kx1.itemnum and i.itemnum=kx2.itemnum and kx1.keywordnum=kt1.keywordnum and kx2.keywordnum=kt2.keywordnum and i.itemdate between '20081101' and '20081130'
Query to list all documents for Document Type # 103 with a December Document Date that exist in Workflow queue # 110 (also display First Name, Last Name, and WF Task Date keyword values):
select itemname, kt1.keyvaluechar, kt2.keyvaluechar, k3.keyvaluedate from hsi.itemdata i, hsi.itemlc il, hsi.keytable101 kt1, hsi.keytable102 kt2, hsi.keyxitem101 kx1, hsi.keyxitem102 kx2, hsi.keyitem129 k3 where i.itemtypenum=103 and i.itemnum=il.itemnum and i.itemnum=kx1.itemnum and i.itemnum=kx2.itemnum and i.itemnum=k3.itemnum and kx1.keywordnum=kt1.keywordnum and kx2.keywordnum=kt2.keywordnum and i.itemdate between '20081201' and '20081231' and il.statenum=110
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