Extracting Categories info from MS SQL DB 1

[AndyH1]

I need to extract the 'categories' information for a set of documents. Does anyone know what SQL statement I would need to run through Query Analyser to get this information for a document, or even better for a number of documnets in a folder?

Any help would be appreciated

Due to financial constraints accessing the db directly is probably the only option (ie we can't buy modules or SDK) I think, unless there is some xmlExport method I can do?

Thanks
Andy

 

[appnair]

This is very simple.This document explains how you write a view to report on categories in the KB

https://knowledge.opentext.com/know...bjId=3498953&objAction=ArticleView&viewType=1

Simply put you are running your report against llattrdata and dtree table which could be very slow at times,but will work regardless.

The categories implemenetation in 9 is a big plus in which
metadata is done.Even the comptetitor to OT ,Documentum is moving along this path I hear(seven years later though) .The reporting is a pain but the view definitely helps.



Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[AndyH1]

Sorry appnair, I must appear awfully stupid, but can't quite understand the Opentext doc.

Iv'e done the query

select catid, catname, attrname, regionname from catregionmap where catname = 'Dokumetregistering';

and get:

CATID CATNAME ATTRNAME REGIONNAME
467122 Dokumetregistering Dokument ID nr. Attr_467122_2
467122 Dokumetregistering Titel Attr_467122_4
467122 Dokumetregistering Dokument dato Attr_467122_5
467122 Dokumetregistering Version nr. Attr_467122_6
467122 Dokumetregistering Forµldet dato Attr_467122_7
467122 Dokumetregistering Dokument udarbejder Attr_467122_8
467122 Dokumetregistering Medie Attr_467122_9
467122 Dokumetregistering Leveringsform Attr_467122_10
467122 Dokumetregistering Referance (f°lgebrev nr.) Attr_467122_11
467122 Dokumetregistering Kommentar Attr_467122_12
467122 Dokumetregistering Omfang /eksemplar/antal sider Attr_467122_13
467122 Dokumetregistering Dokument Type Attr_467122_14


then it says 'create or replace view newcattbl (dataid, Location, Rating, Coop, Skill, SkillYears, Education) as

select a.id, a.valstr, b.valstr, c.valint, d.valstr, e.valint, f.valstr from LLAttrData a, LLAttrData b, LLAttrData c, LLAttrData d, LLAttrData e, LLAttrData f, DTree t where
(t.DataID=a.ID and a.DefID=467122 and a.AttrID=2 and t.VersionNum=a.VerNum) and
(t.DataID=b.ID and b.DefID=467122 and b.AttrID=3 and t.VersionNum=b.VerNum) and
(t.DataID=c.ID and c.DefID=467122 and c.AttrID=4 and t.VersionNum=c.VerNum) and
(t.DataID=d.ID and d.DefID=467122 and d.AttrID=8 and t.VersionNum=d.VerNum) and
(t.DataID=e.ID and e.DefID=467122 and e.AttrID=9 and t.VersionNum=e.VerNum) and
(t.DataID=f.ID and f.DefID=467122 and f.AttrID=6 and t.VersionNum=f.VerNum);

Replace the "467122" with the value of CATID from the query you did in step 1 (done this).

but I'm stuck what it meams when it says:

"Replace the attribute Ids with those from your query."

Could anyone show me how this should appear in the above query?

 

[appnair]

I sent you an email Please check it.In any case what they are saying is when you create the view you give it a name so I could replace this by
'create or replace view newcattbl (dataid, Dokument_ID_nr, Titel, and so on
So if Dokument ID nr. Attr_467122_2
then your attribute ID is 2 and so on

If the attribute is string you use valstr
If the attribute is int you use valint and so on.only you will know how to formulate the query .If you go to the category definition it will have the types in the gui itself.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[MikeOliverAZ]

Just a tip, the trick to llattrdata is that it is twisted from what you might expect and there is one row per attribute per node, so a category with four attributes will have four rows per node.

So once you understand that you can create a view that spins that into one row of four columns per node and report against that view.

I hope that helps, I am not at my workstation so I cannot give you any sql statement code.

Ollie