Cats/Atts query example from Schema Course (95-209) on Nov 13, 2006, Phoenix LinkUp

John_Postma

As promised to some of the Schema and LiveReport course attendees at LinkUp, here's an example of a query that pulls documents and their associated attributes for a given category without using multiple llattrdata table joins.SELECT dtree.dataid "ID", dtree.NAME "Name", a.respondant y, kuaf.name, a.response_desc, a.followup FROM kuaf, dtree, (SELECT ID, vernum, max(case attrid when 2 then valint end) respondant, max(case attrid when 3 then valstr end) response_desc, max(case attrid when 4 then valint end) followup FROM llattrdata WHERE DEFID = 1234 AND DEFVERN = 1 GROUP BY ID, vernum) a WHERE a.ID = dtree.dataid and a.responsdant = kuaf.id and a.VERNUM = dtree.VERSIONNUM AND SUBTYPE = 144 ORDER BY dtree.NAMEThis example requires customizing the query to the category definition, but can perform better than multiple LLAttrData joins when deailing with large volumes of attribute data and categories with a large number of attributes.As with all postings to this discussion, ensure thorough testing and timings are done on a test system before using any query or report examples.Regards,John PostmaPrincipal Database SpecialistOpen Text Corporation

Lucille_Semeniuk

great example. How do you handle those troublesome 'vallongs' ??