Perhaps this will help someone.
/* lists the internalDocID (document number) for all documents containing uploaded files,
excluding those that have been deleted
JiveDocumentBody does not link directly to jiveDocument; it must go through JiveDocBodyVersion
The JiveDocumentBody table contains the actual documents in your system.
Has a uploaded binary: jiveDocumentBody.fileName is not NULL
The jiveDocVersion table has a state column that indicates the current status of a doc
SELECT distinct d.internalDocID
FROM jiveDocument D,
d.internalDocID = BV.internalDocID
and b.bodyID = bv.bodyID
and d.internalDocID = dv.internalDocID
and dv.state = 'published' /* an active document */
and B.fileName is not NULL /* has a binary */
group by d.internalDocID
order by d.internalDocID