Report listing path to document along with attribute values
I'm building a report that lists documents in a selected collection. The report data includes the document's full path as well as the values of all attributes in a given category.I have it all working (details below for those that are looking for the same thing) except for multi-valued attributes. How can I collect all the values for a multi-valued into a single ;-delimited value?Current implementation details (Oracle):I'm including these as they can probably help someone. In order to retrieve the full path to a document, we've created a view:CREATE OR REPLACE VIEW MY_DOC_PATHS(DATAID, DOC_PATH)AS (SELECT DATAID, SYS_CONNECT_BY_PATH( NAME, ':' ) AS DOC_PATHFROM DTREESTART WITH PARENTID = -1CONNECT BY PRIOR DATAID = PARENTID)This can then be connected to DTREE via a typical INNER JOIN. Note that there is a performance hit for this. It's noticeable but not prohibitive. Alternatively, you could create a view that includes all fields in DTREE plus the DOC_PATH. This removes the need to use DTREE at all in queries that require a path.To retrieve attribute vlaues, we have another view:CREATE OR REPLACE VIEW MY_MUSIC_ATTS(DATAID, NAME, UPERMISSIONS, SUBTYPE, PERMID, ARTIST, ALBUM, GENRE)AS (SELECT llattrdata.ID dataid, dtree.NAME, dtree.UPERMISSIONS, dtree.subtype, dtree.permid, MAX (CASE WHEN llattrdata.attrid = 1 THEN llattrdata.valstr END ) ARTIST, MAX (CASE WHEN llattrdata.attrid = 2 THEN llattrdata.valstr END ) ALBUM, MAX (CASE WHEN llattrdata.attrid = 3 THEN llattrdata.valstr END ) GENRE FROM llattrdata, dtree WHERE llattrdata.defid = 30565232 AND llattrdata.vernum = dtree.versionnum AND llattrdata.ID = dtree.dataidGROUP BY llattrdata.ID, dtree.NAME, dtree.UPERMISSIONS, dtree.subtype, dtree.permid)Most of this is courtesy of the guys at Resonate. You'll need to update the defid in the WHERE clause to match the DATAID of the category (and, of course, the attribute names/ids).As I mentioned, the problem I'm having now is how to return the multi-valued attributes as a delimited string. If someone could help, it would be appreciated. Otherwise, I'll post if I find the answer myself.
