OnBase and SQL - Database Reporting Guide - Foundation 23.1 - Foundation 23.1 - Ready - OnBase - external

Database Reporting Guide

Platform
OnBase
Product
Database Reporting Guide
Release
Foundation 23.1
License

Keyword values for examples below:
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=101

Query 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