Clarity

 View Only
  • 1.  Need a query to list projects without documents

    Posted Dec 29, 2011 07:37 PM
    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


  • 2.  RE: Need a query to list projects without documents

    Posted Dec 30, 2011 03:06 AM
    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


  • 3.  RE: Need a query to list projects without documents
    Best Answer

    Posted Jan 03, 2012 01:39 PM
    Thanks Sundar. You saved my day.

    Important: the document must be inside a folder.

    Regards,

    Joni


  • 4.  RE: Need a query to list projects without documents

    Posted Jan 05, 2012 10:17 AM
    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]