Document list w/ version added and owner

Message from <Dave.Beliveau@sanmina-sci.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">Document list w/ version added and owner

I am using the following sql to try to list all documents in a set of folders and show who added the current version and who the creator of the document is however I am getting a list of all versions added. I only want the most recent version and the original creator....any ideas on how to get this?

select d.name 'Folder Name',
        a.auditdate 'Audit Date',
        b.name 'Document Name',
        b.versionnum 'Version',
        c.name 'Version Added By',
        f.name 'Creator'
from dauditnew a,
        dtree b,
        kuaf c,
        dtree d,
        dversdata e,
        kuaf f
where a.dataid=b.dataid and
        d.dataid=b.parentid and
        a.performerid=c.id and
        b.dataid=e.docid and
        e.version=b.versionnum and
        e.DocID=b.dataid and
        a.subtype=144 and
        f.id=b.createdby and
        a.auditstr='AddVersion' and
        b.parentid in (8003700,7216247,7216243,7216244,7216245,7216246)
order by b.parentid, b.name, a.auditdate asc

We are using MS SQL 2000 w/ livelink version 9.5 sp-1

Thanks,
Dave Beliveau

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited. If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING. Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina-SCI Corporation (or any of its subsidiaries), or any other person or entity.
_____________________________________________________________________________
Scanned by Sanmina-SCI eShield _____________________________________________________________________________

Find more posts tagged with

Comments

volvostephen

Message from Fisher, Stephen via eLinkI don't think you need dAudit (DAuditNew) and I think that is where theproblem came in. Try this query.select d.name 'Folder Name', b.createDate 'Audit Date', b.name 'Document Name', b.versionnum 'Version', c.name 'Version Added By', f.name 'Creator' from dtree b, kuaf c, dtree d, dversdata e, kuaf f where d.dataid=b.parentid and e.owner=c.id and b.dataid=e.docid and e.version=b.versionnum and e.DocID=b.dataid and b.subtype=144 and f.id=b.createdby and b.parentid in (8003700,7216247,7216243,7216244,7216245,7216246) order by b.parentid, b.name asc -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: August 11, 2006 9:01 AMTo: eLink RecipientSubject: Document list w/ version added and owner*****************************NOTICE - This email (including all attachments) is the sole property of the HATCH group of companies. It may contain personal and confidential information, and is intended solely for the addressee. If you are not the intended recipient, any use, disclosure, copying or any other action is unauthorized and may be unlawful. This email is also subject to copyright. No part of it should be reproduced, adapted or communicated without the written consent of the relevant HATCH company.If you have received this email in error, please notify us immediately by return e-mail or via mailto:MailAdmin@Hatch.ca and delete all copies from your system.