SQL based Custom Query that returns a document results list of documents with large file size pages

[Jason_Powers]

I’m trying to help a customer who is searching for a number of documents that had been scanned at an extreme resolution and resulting in very large page file sizes on disk in the disk groups.  Ideally we’d like to get results in the thick client in the form of a document results list via a custom query. I’ve assumed if this is possible at all, this would be a SQL query based custom query. I’ve been able to construct a rudimentary SQL query (below) that selects from the itemdatapage table based on a filesize threshold, and which returns document handle values as results.  While this works great as a direct SQL query, it doesn’t translate into the OnBase thick client custom query mold, as it doesn’t select from the itemdata table. 

Is there any way to translate this into a SQL query that would operate within a SQL based custom query, leveraging the same type of page file size criteria, and providing results in the form of a document results list in the custom query screen? OnBase version EP3.

select distinct hsi.itemdatapage.itemnum from hsi.itemdatapage

where hsi.itemdatapage.filesize > 37000000

order by hsi.itemdatapage.itemnum

Thanks for any and all feedback.

[Barbara_Ebel-La]

That should be all you need, but it may run for a while.  If you know the specific file type it was uploaded as, there should be a page in the SysAdmin MRG that tells you the values for the file types in the FILETYPENUM column.  Ex: PDF = 16

If you want to constrain by date range, you can try this:

select i.itemnum, idp.filesize, i.datestored
from hsi.itemdatapage idp
inner join hsi.itemdata i on i.itemnum = idp.itemnum
and i.datestored >= sysdate - 30
-- and filetypenum = 16 -- PDF file type
order by filesize desc;

[Jason_Powers]

Thanks for reaching out Barbara.  While my itemdatapage query will return results, it does not lend itself to composing in a custom query because it does not select on the itemdata table.   Our goal is to not have to run this in SQL, but rather via a custom query in the OnBase client, such that it results a document results list, just as if a user has searched for documents in the client interface.

I'm wondering if there is a way to re-envision the SQL query such that it still does what it is supposed to do, but selects on the itemdata table, rather than the itemdatapage table, so it might be a candidate for SQL based custom query.

[Elizabeth_Bond · Accepted solution]

Hi Jason,

What we have had to do in situations like these is query the database to get the document ID's then make a custom query that just pulls up those doc id's. Something like:

SELECT * FROM his.itemdata WHERE his.itemdata.itemnum in (doc handle, doc handle, doc handle)

Unfortunately, I haven't found a better way to pull off needs like these while still keeping the document viewer from custom queries so we can see items quickly. It ain't a great resolution but it would work the downside is you'd still need someone to query the data in the database every once in awhile to add these document ID's if this is going to end up being a maintenance task. If you do figure out a better way please let us know!

[Jason_Powers]

Thanks so much Beth, I think this will work for us.