Need a query to list projects without documents

[Legacy User]

Our Project manager must upload document in Projects.

I need a query that list all projects that does not have any documents in the document area.

Any idea ?

Thanks

Joni

[Anon Anon]

Joni,

Please find the below query.Test this query.

Clarity:- 12.1.1
DB:- Oracle 10.2.0.4

select code project_code
from inv_investments inv where odf_object_code = 'project' and
inv.id not in
(
select distinct uv.id project_id
FROM
CLB_DMS_FOLDERS FOL , CLB_DMS_FILES FIL,clb_dms_versions ver,
CLB_DMS_FOLDERS FOL1, inv_investments UV
WHERE UV.ID = FOL.ASSOC_OBJ_ID AND FOL.ID = FOL1.PARENT_FOLDER_ID
and fil.id = ver.file_id
AND FOL1.ID=FIL.PARENT_FOLDER_ID(+)
AND FIL.NAME IS NOT NULL
)

pls look at the KB too.

https://comm.support.ca.com/?legacyid=TEC540993

cheers,
sundar

[Legacy User]

Thanks Sundar. You saved my day.

Important: the document must be inside a folder.

Regards,

Joni

[Anon Anon]

Very nice Sundar.

I changed the query to use SQL Server and JOIN syntax instead:

[font=Courier New]SELECT ii.code Project_Code,
ii.name Name
FROM INV_INVESTMENTS ii
WHERE odf_object_code = 'project'
AND ii.is_active = 1 -- filter to active projects only
AND ii.id NOT IN -- change this to IN to get project with documents
(

SELECT DISTINCT ii2.id project_id

FROM CLB_DMS_FOLDERS FOL

INNER JOIN CLB_DMS_FOLDERS FOL1 ON FOL1.PARENT_FOLDER_ID = FOL.ID

INNER JOIN CLB_DMS_FILES FIL ON FOL1.ID = FIL.PARENT_FOLDER_ID

INNER JOIN CLB_DMS_VERSIONS ver ON ver.file_id = fil.id

INNER JOIN inv_investments ii2 ON ii2.ID = FOL.ASSOC_OBJ_ID

WHERE FIL.NAME IS NOT NULL
)[font]