Posted @ 11/27/2012 08:44 AM
Description:
Query that gets the file ID, Project ID, Project name, file name, size, version last updated date, checked out date, checked in date, author, file path and file description, File Status, file category, filetype. This query is for SQL Server.
Solution:
select
f.id file_id,
srm.id project_id,
srm.unique_nameproject_code,
srm.name project_name,
f.name file_name,
ver.byte_size,
ver.version_number version,
ver.last_updated_date,
(case ver.is_latest when ‘1’then (case f.lock_owner_id when 0 then null else f.last_updated_date end ) elsenull end) checked_out_date,
(case ver.is_latest when ‘1’then (case f.lock_owner_id when 0 then ver.version_created_date else null end )else ver.version_created_date end) checked_in_date,
(select full_name fromsrm_resources where user_id=f.lock_owner_id) locked_by,
(case ver.is_latest when ‘1’then (case f.lock_owner_id when 0 then ‘In’ else ‘Out’ end ) else ‘In’ end)Checked_in_out,
(select full_name fromsrm_resources where user_id=f.owner_id) author,
substring(fol.path_name+’/’+fol.name,23,100)path,
f.description description,
(select name fromcmn_lookups_v where language_code=’en’ and lookup_type=’DOC_STATUS’ andlookup_code=ver.status) file_status,
(select name fromcmn_lookups_v where language_code=’en’ and lookup_type=’DOC_CATEGORY’ andlookup_code=f.category) file_category,
f.mime_type file_type,
ver.check_in_comment
from clb_dms_files f
inner join clb_dms_foldersfol on fol.id=f.parent_folder_id
left outer join
(select id,
version_number, status,
file_id, check_in_comment,
byte_size, version_number asversion,
created_by, created_date asversion_created_date,
is_latest
from clb_dms_versions
) ver on ver.file_id=f.id
INNER JOIN SRM_PROJECTS SRMONSRM.unique_name=substring(fol.path_name+’/’+fol.name,23,abs(charindex(‘/’,fol.path_name+’/’+fol.name,23)-23))
INNER JOIN PRJ_PROJECTS PRJON PRJ.PRID=SRM.ID
WHERE
f.language=’en’ andfol.folder_type in (‘ProjectFolder’,’StandardFolder’)
“Solutions provided by CA Technolgies”
