Extract attachment attributes for Items

Hello,

How to extract the details of all the standard attributes in the attachments tab of Parts & Documents for all the revisions using SQL query against Agile 9.3.4?

Thanks.

Agile User Asked on January 5, 2022 in Product Collaboration.
Add Comment
1 Answer(s)

-- First part of select is for individual files on the attach tab (not folders)
SELECT
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000)
then (select change.change_number from agile.change where agile.ATTACHMENT_MAP.PARENT_ID=change.id)
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select item.item_number from agile.item where agile.ATTACHMENT_MAP.PARENT_ID=item.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1442)
then (select manufacturers.name from agile.manufacturers where agile.ATTACHMENT_MAP.PARENT_ID=manufacturers.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1483)
then (select manu_parts.part_number from agile.manu_parts where agile.ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id)
else
null end as Obj_Number,
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select REV.REV_NUMBER from agile.REV where REV.ITEM = agile.ATTACHMENT_MAP.PARENT_ID AND REV.CHANGE = ATTACHMENT_MAP.PARENT_ID2)
WHEN ATTACHMENT_MAP.PARENT_CLASS in (1483)
THEN ( select name from agile.manufacturers where id =
(select manu_id from agile.manu_parts where agile.ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id ) )
ELSE
NULL END AS REV_NBR,
NODETABLE.DESCRIPTION as Obj_Class,
ATTACHMENT.ATTACHMENT_NUMBER,
VERSION_FILE_MAP.DESCRIPTION,
ATTACHMENT.CREATE_DATE AS Att_Created_Date,
ATTACHMENT.ID AS ATT_ID,
VERSION.ID AS VER_ID,
VERSION.VERSION_NUM,
ATTACHMENT_MAP.LATEST_VSN,
FILES.FILENAME,
FILES.FILE_TYPE,
FILES.FILE_SIZE,
FILES.CREATED AS FILE_CREATED,
FILES.LAST_UPD AS FILE_LAST_UPD,
FILES.ID as FILE_ID,
FILE_INFO.IFS_FILEPATH,
FILE_INFO.HFS_FILEPATH
FROM agile.ATTACHMENT
INNER JOIN agile.VERSION
ON VERSION.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.VERSION_FILE_MAP
ON VERSION_FILE_MAP.VERSION_ID = VERSION.ID
INNER JOIN agile.FILES
ON VERSION_FILE_MAP.FILE_ID = FILES.ID
INNER JOIN agile.ATTACHMENT_MAP
ON ATTACHMENT_MAP.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.NODETABLE
ON NODETABLE.ID = ATTACHMENT_MAP.PARENT_CLASS
LEFT OUTER JOIN agile.FILE_INFO
ON FILE_INFO.FILE_ID = FILES.ID
WHERE ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000,9000,10000,1442,1483)
AND VERSION.ID = ATTACHMENT_MAP.LATEST_VSN
AND ATTACHMENT.DELETE_FLAG IS NULL
AND ATTACHMENT_MAP.FILE_ID > 0 -- Include this line for when it is one specific file on the attach tab, not a whole folder
AND VERSION_FILE_MAP.FILE_ID = ATTACHMENT_MAP.FILE_ID -- Include this line for when it is one specific file on the attach tab, not a whole folder
UNION ALL
-- Second part of select is for whole folders that exist on attach tab
SELECT
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000)
then (select change.change_number from agile.change where ATTACHMENT_MAP.PARENT_ID=change.id)
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select item.item_number from agile.item where ATTACHMENT_MAP.PARENT_ID=item.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1442)
then (select manufacturers.name from agile.manufacturers where ATTACHMENT_MAP.PARENT_ID=manufacturers.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1483)
then (select manu_parts.part_number from agile.manu_parts where ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id)
else
null end as Obj_Number,
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select REV.REV_NUMBER from agile.REV where REV.ITEM = ATTACHMENT_MAP.PARENT_ID AND REV.CHANGE = ATTACHMENT_MAP.PARENT_ID2)
WHEN ATTACHMENT_MAP.PARENT_CLASS in (1483)
THEN ( select name from agile.manufacturers where id =
(select manu_id from agile.manu_parts where ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id ) )
ELSE
NULL END AS REV_NBR,
NODETABLE.DESCRIPTION as Obj_Class,
ATTACHMENT.ATTACHMENT_NUMBER,
VERSION_FILE_MAP.DESCRIPTION,
ATTACHMENT.CREATE_DATE AS Att_Created_Date,
ATTACHMENT.ID AS ATT_ID,
VERSION.ID AS VER_ID,
VERSION.VERSION_NUM,
ATTACHMENT_MAP.LATEST_VSN,
FILES.FILENAME,
FILES.FILE_TYPE,
FILES.FILE_SIZE,
FILES.CREATED AS FILE_CREATED,
FILES.LAST_UPD AS FILE_LAST_UPD,
FILES.ID as FILE_ID,
FILE_INFO.IFS_FILEPATH,
FILE_INFO.HFS_FILEPATH
FROM agile.ATTACHMENT
INNER JOIN agile.VERSION
ON VERSION.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.VERSION_FILE_MAP
ON VERSION_FILE_MAP.VERSION_ID = VERSION.ID
INNER JOIN agile.FILES
ON VERSION_FILE_MAP.FILE_ID = FILES.ID
INNER JOIN agile.ATTACHMENT_MAP
ON ATTACHMENT_MAP.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.NODETABLE
ON NODETABLE.ID = ATTACHMENT_MAP.PARENT_CLASS
LEFT OUTER JOIN agile.FILE_INFO
ON FILE_INFO.FILE_ID = FILES.ID
WHERE ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000,9000,10000,1442,1483)
AND VERSION.ID = ATTACHMENT_MAP.LATEST_VSN
AND ATTACHMENT.DELETE_FLAG IS NULL
AND ATTACHMENT_MAP.file_id = 0 -- Include this line for when it IS a whole folder on the attach tab

Agile Talent Answered on January 7, 2022.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.