Category Version

[ujjval090683]

How can I retrive Category Version information using Oracle Query?
I am using below QUery to retrive category information. But Versions are not comming with that.

select CatTbl.subtype, CatMasterTbl.dataid as CatID, CatMasterTbl.name as CatName, CatParentTbl.name as CatParentName, count(distinct DocCountTbl.ID) as DocCount, count(distinct FileCountTbl.filetype) as CountFileType,wm_concat( distinct FileCountTbl.filetype) as FileTypes, DocCountSupTbl.subtype AS DocSubStype1,FileCountTbl.subtype as DocSubStype2,(SUM(FileCountTbl.datasize)/count(DocCountTbl.ID))/1024/1024/1024 as TSIZE, deleteddocs.nodeid as IfDeleted
from webnodes CatMasterTbl
LEFT OUTER JOIN dtree CatTbl
ON CatMasterTbl.dataid = CatTbl.dataid
LEFT OUTER JOIN dtree CatParentTbl
ON CatTbl.parentid = CatParentTbl.dataid
LEFT OUTER JOIN CatRegionMap
ON CatMasterTbl.dataid = CatRegionMap.CatID
LEFT OUTER JOIN LLAttrData DocCountTbl
ON DocCountTbl.DefId = CatRegionMap.CatID
LEFT OUTER JOIN Dtree DocCountSupTbl
ON DocCountTbl.ID = DocCountSupTbl.dataid
LEFT OUTER JOIN webnodes FileCountTbl
ON FileCountTbl.dataid = DocCountTbl.ID
LEFT OUTER JOIN deleteddocs
ON DocCountTbl.ID = deleteddocs.nodeid
where CatMasterTbl.subtype='131'
and DocCountTbl.ID in (select dataid from dtreeAncestors where AncestorID=2000)
GROUP BY deleteddocs.nodeid, CatTbl.subtype, DocCountSupTbl.subtype,FileCountTbl.subtype, Catregionmap.catid,DocCountTbl.DefId ,CatRegionMap.CATNAME, CatMasterTbl.dataid, CatMasterTbl.name , CatParentTbl.name
ORDER BY CATID

ALso, for each versions the CAT ID is same, then how can I get information about the versions attribute? What all changes are made etc using Query?

 

[ggriffit]

the data itself is stored in a group of tables, including LLATTRDATA, LLBLOBDATA and CATREGIONMAP, you will need to look at these tables to solve your query.

the definition of the category is stored in a file in your EFS and is not written to the DB, although it can be infered upto a point, e.g. if you select all the attributes and attribute 1 in the category comes back as the VALSTR its probably a string etc.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/