AnsweredAssumed Answered

HELP - Need help with "joins" in my Query

Question asked by mscann on Oct 3, 2012
Latest reply on Oct 9, 2012 by Chris_Hackett
Hello,

I'm attempting to get output from the database, relating to Documents in Clarity, and I'm having issues with the Joins of some "last minute" information that I need to include. I know where the information/data resides but I can't get the "joins" correct.

I need to include the following:
1. User Names and User Ids: The Users Ids I need to disply are in the CLB_DMS_VERSIONS table in the CREATED_BY column. The CREATED_BY column is a numeric value and I do not see any data for Names in this Table (Or any of the Document tables, CLB_DMS_**). The only place that I know of that contains data for User Names is in the CMN_SEC_USERS Table under the column "USER_NAME."
2. Version "Date, Time" stamp: Table=CLB_DMS_FILES, Column=“Created_Date.
3. Version "Checked-In By": Table=CLB_DMS_FILES, Column=Created_By
4. Version "Comments":
Table=CLBS_DMS_VERSIONS, Column=“CHECK_IN_COMMENT”
5. Collaboration Path: I need to pull in the collaboration path for the Project: example: /Root/DMS/PM/Projects/123456/123 Meeting Notes. I'm not sure were this path is. I'm only able to locate the "root
PATH under Table=CLB_DMS_FOLDERS, Column="PATH". (I have this PATH in my query below but it's not what I need). I need the full Path to the Document name - "123 Meeting Notes."


Can someone help me with getting the "joins" in my existing query below? (This query works on it's own - - again I need to add the additional information).

I need to add it into this query that I started:
SELECT
INV.NAME INV_PROJECT_NAME,
INV.CODE PROJECT_ID,
VER.VERSION_NUMBER VERSION,
VER.LAST_UPDATED_DATE LAST_UPDA,
VER.BYTE_SIZE BYTE_SIZE,
VER.IS_LATEST VER_IS_LATEST,
FLD.PATH_NAME PATH,
FIL.NAME FILE_NAME

FROM CLB_DMS_VERSIONS VER
left JOIN CLB_DMS_FILES FIL ON VER.FILE_ID = FIL.ID
left JOIN CLB_DMS_FOLDERS FLD ON FIL.PARENT_FOLDER_ID = FLD.ID
left JOIN INV_INVESTMENTS INV ON FLD.ASSOC_OBJ_ID = INV.ID

where trunc(ver.last_updated_date) < '2011-10-01'
and ver.is_latest=0



Thanks much!

Outcomes