Retrieving most recent category version against a node

I am trying to write an SQL report to retrieve cats and atts data against specific nodes within LL9.2.0, but I am running into a brick wall where multiple versions of category data exist against a particular node. We made an assumption that DTREE.VERSIONNUM = LLATTRDATA.VERNUM should retrieve the latest category data, but it only retrieves the cat data that the node was originally stored with. Can someone point me in the right direction for how to retrieve the latest version of a category against a document?

Find more posts tagged with

Comments

Francisco_Alcala-Soler_(iaea_001user3_-_(deleted))

Message from via eLinkHi,What SQL conditions are you using in your SQL report's WHERE clause to identify the correct attribute values?Using DTREE.VERSIONNUM = LLATTRDATA.VERNUM should get you the value of a node's current category/attributes values, which should not necessarily correspond to the latest version of the category, since upgrading categories on individual nodes is simply optional.If your problem is not one of the category not having been upgraded on your particular node, I would make sure that the above condition is paired with DTREE.DATAID = LLATTRDATA.ID and DTREE.DATAID = MY_DOC_NODE_ID. If somehow the DTREE.VERSIONNUM that is slipping in is not the one from your document node, but the one from the category itself, which is also a node in DTREE, and your category is at a version lower than your document node (and your document node with the same version number as the category points to the original category version :-), then you might be actually getting the wrong values you describe.Let us see a bit of your code in case we can further help.Regarding your last question about retrieving "the latest version of a category against a document," I think there is a caveat. It can be easily done with only SQL statements, if the document node's category has actually been upgraded to the latest version of the said category. You might already know that the table CATREGIONMAP contains the necessary attribute names and IDs to pair them with their values. If your categories are stable after development or if after each modification all documents are upgraded, then this should be no problem.On the other hand, if the document node is not on the latest version of the category, then you can still easily obtain attribute values and IDs from LLATTRDATA, but I don't think you can easily retrieve attribute names, because CATREGIONMAP contains only information about the latest version of a category, as to speed up some processing, since I think the real description of a particular category/attribute version is actually stored in a file as the string representation of an Oscript assoc or something similar. This info can't be accessed with only SQL.I hope I am not misrepresenting anything. Please, anybody else correct me if I'm wrong or if there is any other way to access the required information.Regards, Curro> -----Original Message-----> From: eLink Discussion: Development Discussion> [mailto:development@elinkkc.opentext.com]> Sent: Monday, 03 January, 2005 23:19> To: eLink Recipient> Subject: Retrieving most recent category version against a node> > > Retrieving most recent category version against a node> Posted by Carmela Vinaccia, Carmela on 01/03/2005 05:18 PM> > I am trying to write an SQL report to retrieve cats and atts > data against specific nodes within LL9.2.0, but I am running > into a brick wall where multiple versions of category data > exist against a particular node. We made an assumption that > DTREE.VERSIONNUM = LLATTRDATA.VERNUM should retrieve the > latest category data, but it only retrieves the cat data that > the node was originally stored with. Can someone point me in > the right direction for how to retrieve the latest version of > a category against a document?> > [To reply to this thread, use your normal E-mail reply function.]> > ============================================================> > Discussion: Development Discussion> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=786303&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exeThis email message is intended only for the use of the named recipient.Information contained in this email message and its attachments may beprivileged, confidential and protected from disclosure. If you are not theintended recipient, please do not read, copy, use or disclose thiscommunication to others. Also please notify the sender by replying to thismessage and then delete it from your system.

Benedict_Chiu

Hi Curro,Many thanks for your suggestions - I am still very new to Livelink and am beginning to find it hides useful information in strange places! Please find attached the entire SQL that runs - it is a bit ugly, and SEEMS to do the job, but we have found half a dozen examples or so where the category data on a document has been changed (pick list value changed at an individual document level), but the report only pulls the original value that was saved when the object was first stored. Hence, if you go and change the value of a category attribute on the document after the document has been indexed, that change is not reflected in this report.Our categories are stable after development, but sometimes the values set at an individual node level are not (in these cases!). Anything you can offer in the way of suggestions or a shove in the right direction would be greatly appreciated!RegardsAndrew O :-0)SE Water Australia

Francisco_Alcala-Soler_(iaea_001user3_-_(deleted))

Message from via eLinkHi Andrew,Three things come to mind:1) The condition (LLATTRDATA_14.DEFID = 9979578) seems to be missing in your WHERE clause. Perhaps DOC_PREFIX is the value whose changes are not reflected on the report?2) If the above is not the case, could you please send me the result of executing the following?: SELECT CatRegionMap.*, DTree.VersionNum FROM CatRegionMap, DTree WHERE CatID = 9979578 AND CatID = DataIDYou can analyze the results yourself, if you want. Make sure that the LLAttrData.AttrID values which you are using in your statement actually correspond to the last number of the value in CatRegionMap.RegionName for each one of the attribute names.3) If that is all OK, then make sure that all your documents have been upgraded to the latest version of your category. To figure whether this is needed, you can execute:SELECT DISTINCT DTree1.Name, DTree1.DataID, DTree1.ParentID, LLAttrData.DefVerN as CurrentCatVersion, DTree2.VersionNum as LatestCatVersionFROM DTree DTree1, DTree DTree2, LLAttrDataWHERE DTree1.SubType = 144 AND LLAttrData.DefID = 9979578 AND DTree1.DataID = LLAttrData.ID AND DTree1.VersionNum = LLAttrData.Vernum AND DTree2.DataID = LLAttrData.DefID AND LLAttrData.DefVerN < DTree2.VersionNumand if you get any results it means that some of your documents may need upgrading.Let me know how it goes.Regards, Curro> -----Original Message-----> From: eLink Discussion: Development Discussion> [mailto:development@elinkkc.opentext.com]> Sent: Tuesday, 04 January, 2005 23:00> To: eLink Recipient> Subject: Hi Curro,> > > Hi Curro,> Posted by Carmela Vinaccia, Carmela on 01/04/2005 04:59 PM> > Hi Curro,> > Many thanks for your suggestions - I am still very new to > Livelink and am beginning to find it hides useful information > in strange places! Please find attached the entire SQL that > runs - it is a bit ugly, and SEEMS to do the job, but we have > found half a dozen examples or so where the category data on > a document has been changed (pick list value changed at an > individual document level), but the report only pulls the > original value that was saved when the object was first > stored. Hence, if you go and change the value of a category > attribute on the document after the document has been > indexed, that change is not reflected in this report.> > Our categories are stable after development, but sometimes > the values set at an individual node level are not (in these > cases!). Anything you can offer in the way of suggestions or > a shove in the right direction would be greatly appreciated!> > Regards> Andrew O :-0)> SE Water Australia> > [To reply to this thread, use your normal E-mail reply function.]> > ============================================================> > Attachment link: sql-QMS report.doc> https://knowledge.opentext.com/knowledge/livelink.exe/3797894/sql-QMS_report.doc?func=doc.Fetch&nodeid=3797894============================================================Topic: Retrieving most recent category version against a nodehttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3796224&objAction=viewDiscussion: Development Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=786303&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exeThis email message is intended only for the use of the named recipient.Information contained in this email message and its attachments may beprivileged, confidential and protected from disclosure. If you are not theintended recipient, please do not read, copy, use or disclose thiscommunication to others. Also please notify the sender by replying to thismessage and then delete it from your system.

Benedict_Chiu

Hi Curro, Thanks for the followup thoughts - it's keeping me sane at the moment! Re your points:1) This condition is in the WHERE clause, it's just hiding on the next line down2) See attached HTML output from a TOAD session3) This does show me a number of documents, but investigation revealed these to be in people's personal workspaces and are not included in the report. The category attribute that is reporting incorrectly is audit_freq (Attr_9979578_30), it is reporting the original value, not the current value as updated against individual documents. Is there anything else you can possibly think of that may be causing this?Cheers,Andrew O :-)

Francisco_Alcala-Soler_(iaea_001user3_-_(deleted))

Message from via eLinkAndrew,I am running out of ideas. The attribute IDs seem to be all correct. I would figure out the DataID of the troublesome document and make sure that it is on the latest version of the category (46 in your case). Just in case and if you want, send me the output of: SELECT * FROM LLAttrData WHERE ID = THE_ID_OF_YOUR_PROBLEM_DOCUMENTand let me know what values you expect and actually get for its AUDIT FREQUENCY attribute in your report. Maybe we see any inconsistency, but this is more a guess than anything else... :-(Just in case, I would also run a database consistency check from the Admin.Index pages and if nothing comes out, contact OT Customer Support. Maybe the LLATTRDATA table does not actually work the way we suppose it does...Regards, Curro> Thanks for the followup thoughts - it's keeping me sane at > the moment! Re your points:> 1) This condition is in the WHERE clause, it's just hiding on > the next line down> 2) See attached HTML output from a TOAD session> 3) This does show me a number of documents, but investigation > revealed these to be in people's personal workspaces and are > not included in the report. > > The category attribute that is reporting incorrectly is > audit_freq (Attr_9979578_30), it is reporting the original > value, not the current value as updated against individual > documents. Is there anything else you can possibly think of > that may be causing this?This email message is intended only for the use of the named recipient.Information contained in this email message and its attachments may beprivileged, confidential and protected from disclosure. If you are not theintended recipient, please do not read, copy, use or disclose thiscommunication to others. Also please notify the sender by replying to thismessage and then delete it from your system.