LLAttrData, DVersData, DTree join

Hi. I'm trying to create a simple (I think) query that joins LLAttrData, DVersData and DTree to return the most recent version of a document but searching on a particular attributes value in the VALSTR column of LLAttrData?

Hope someone can help.

Thanks,

Find more posts tagged with

Comments

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Cathy

What do you have so far?

Do you already know the Category and Attribute IDs so that you can provide the right 'where' clauses?

What information do you want to get from DVersData?

It might be simpler to use Livelink search, by the way.

To help work out what you need from LLAttrData query the CatRegionMap table.

This is a table that Livelink / Content Server maintains only so that you can find out how an attribute and category is named internally.

select * from catRegionMap order by 1,5

e.g.

     CATID CATNAME              SETNAME    ATTRNAME             REGIONNAME
---------- -------------------- ---------- -------------------- -----------------
     45761 man cat                         details              Attr_45761_2
    100762 Mand1                           String               Attr_100762_2
    244305 New Category                    Pets                 Attr_244305_2
    268723 AE-Lookup                       Col64                Attr_268723_2
* 292483 Cat Test                        Text1                Attr_292483_2   *
    292483 Cat Test                        Integer2             Attr_292483_3
    292483 Cat Test                        Date3                Attr_292483_4
    292483 Cat Test                        Multiline4           Attr_292483_5
    292483 Cat Test                        Popup5               Attr_292483_6
    292483 Cat Test                        TPopup6              Attr_292483_7

CatID is the integer key for the named category and RegionName is constructed from "Attr_" <CatID> "_" <AttrID>

So to use the example above, the CatID data is stored in LLAttrData in the DefID column...

select ID, Valstr from llattrdata where defid=292483 and AttrID=2;

This would return the DataID (in ID) and the string value for every item that has this attribute applied.

So you just want to join the other table/s

select DataID, Name, Versionnum Subtype, Valstr as "Text1" from DTree, LLAttrData where DataID = ID and defid=292483 and AttrID=2;

    DATAID NAME                VERSIONNUM    SUBTYPE Text1
---------- ------------------- ---------- ---------- ------------
     53240 01 New Folder                0          0 Chicken
    182594 context views.doc            5        144 Chips

Does this look like the sort of output you want to see?

If you want to add in DVersData information, just join in that table on an appropriate columns....

I will qualify the columns with table aliases so it is more apparent which table each output column is from....

select D.DataID, D.Name, D.Versionnum, D.Subtype, A.Valstr as "Text1", DV.MimeType

from DTree D, LLAttrData A, DVersData DV
where D.DataID=A.ID

and D.DataID=DV.Docid and D.VersionNum=DV.Version

and A.defid=292483 and A.AttrID=2

I separated out the join conditions on different lines so I can explain them.

One row in DTree has only one match in LLAttrData.

But for DTree and DVersData there will be on DVersData row per version of the Dtree DataID, so we only want the latest DVersData row and therefore need to join on two columns so we only get one row in our output.

DATAID NAME VERSIONNUM SUBTYPE Text1 MIMETYPE
---------- ------------------------------ ---------- ---------- ------------ --------------------
182594 context views.doc 5 144 Chips application/msword

Notice that this time, the Folder reported earlier does not appear as it does have an entry in DVersData.

If you want those 'missing rows' you need to do outer joins... the technique varies according to whether you are using MS SQL or Oracle.

Regards

Lindsay

European Escalation Team

Open Text UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 August 09, Tue 19:51
To: eLink Recipient
Subject: LLAttrData, DVersData, DTree join

LLAttrData, DVersData, DTree join

Posted by cathy.kelly@huskyenergy.com (Kelly, Cathy) On 2011/08/09 14:48

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Livelink Server:	knowledge-wlweb01

Cathy_Kelly

Hi. Thanks for the reply. Esentially what I would like to do is:

select id, valstr from llattrdata

where defid = 162584

and (attrid = 11 and valstr = 'Test documents')

and attrid = 20

But when I run this it returns all versions. I want to add the other tables just to be able to return the value that resides in attrid 11 and 20 for the most recent version of a document.

I hope that makes sense - I dont' think it's a very complicated query - it's just I don't know how to join the tables and display the information I'm looking for. I tried using a subquery but with that you can only select one field.

Thanks again.

Cathy

praveen_nagarajan

Hi,

I guess the following query might fetch you the desire result.

SELECT id, valstr FROM llattrdata WHERE defid = 162584 AND (attrid = 11 AND valstr = 'Test documents') AND attrid = 20 AND vernum = (

?SELECT MAX(vernum) FROM llattrdata WHERE defid = 162584 AND (attrid = 11 AND valstr = 'Test documents') AND attrid = 20 )

Let me know if it helps.

Best Regards

Praveen

On Fri, Aug 12, 2011 at 12:13 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

LLAttrData, DVersData, DTree join ? Posted bycathy.kelly@huskyenergy.com (Kelly, Cathy)On 2011/08/11 14:41
?
Hi.? Thanks for the reply.? Esentially what I would like to do is:select id, valstr from llattrdatawhere defid = 162584and (attrid = 11 and valstr = 'Test documents')and attrid = 20But when I run this it returns all versions.? I want to add the other tables just to be able to return the value that resides in attrid 11 and 20 for the most recent version of a document.I hope that makes sense - I dont' think it's a very complicated query - it's just I don't know how to join the tables and display the information I'm looking for.? I tried using a subquery but with that you can only select one field.Thanks again.Cathy
[To post a comment, use the normal reply function]
Topic: LLAttrData, DVersData, DTree join
Forum: Content Server LiveReports Forum
Livelink Server: knowledge-wlweb01

Hans Stoop

I think this will work:

select a.id, a.valstr from llattrdata a, DTree d

where defid = 162584

and (attrid = 11 and valstr = 'Test documents')

and attrid = 20 and a.id = d.DataID and a.VerNum = d.VersionNum

I do this oottomh so a may make some naming errors, but at least you get the idea how it should work.

Hans

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Donnerstag, 11. August 2011 20:43
To: eLink Recipient
Subject: LLAttrData, DVersData, DTree join

LLAttrData, DVersData, DTree join

Posted by cathy.kelly@huskyenergy.com (Kelly, Cathy) On 2011/08/11 14:41

Hi. Thanks for the reply. Esentially what I would like to do is:

select id, valstr from llattrdata

where defid = 162584

and (attrid = 11 and valstr = 'Test documents')

and attrid = 20

But when I run this it returns all versions. I want to add the other tables just to be able to return the value that resides in attrid 11 and 20 for the most recent version of a document.

Thanks again.

Cathy

[To post a comment, use the normal reply function]
Topic:	LLAttrData, DVersData, DTree join
Forum:	Content Server LiveReports Forum
Livelink Server:	knowledge-wlweb01

Cathy_Kelly

Thanks guys but neither of those works. If I try to display attrid = 20 then that's where it finds nothing. If I comment that out - it finds the documents but only the ones with valstr = 'Test document'. Basically I want to display more than one attrid valstr but based on vastr??

Cathy

Cathy_Kelly

Hi Lindsay.

Your code (listed below) worked great to return the most recent version with the valstr being displayed but...I would like to display another ATTRID's value for valstr??? EX. ATTRID = 7 has a value of 'This is the second test document'.

Hope you can help!

Cathy

select

D . DataID , D .Name, D . Versionnum , D .Subtype, A. Valstr as "Text1" , A. AttrID , A. defID from DTree D , LLAttrData A, DVersData DV where D . DataID = A.ID and D . DataID = DV . Docid and D . VersionNum = DV .Version and A. defid = 162584 and A. AttrID = 27

and A. valstr = 'Test Documents'

Lindsay_Davies

Hi Cathy,

You need to use OR not AND.

Think of looking at one row at a time in the LLAttrData table.

AttrID can only have 1 value in that row.

So you want the row reported when ATTRID = 13 OR ATTRID = 20

No row can exist where ATTRID = 1 AND ATTRID=2 (at the same time)

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Fri 12/08/2011 15:20
To: eLink Recipient
Subject: LLAttrData, DVersData, DTree join 2

LLAttrData, DVersData, DTree join 2

Posted by cathy.kelly@huskyenergy.com (Kelly, Cathy) On 2011/08/12 10:16

[To post a comment, use the normal reply function]
Topic:	LLAttrData, DVersData, DTree join
Forum:	Content Server LiveReports Forum
Livelink Server:	knowledge-wlweb01

Josephine_Bush

hi, i stumbed upon this post when looking for something else. not sure if you still need an answer but this is something i've done to get multiple valstr in a query. you have to join in the llattrdata twice (or as many times as you need different valstr) - each with a different alias

select

ISNULL ( l3 . valstr , ' ' ) as revisionno , ISNULL ( l4 . valstr , ' ' ) as status , d .* from llattrdata dtree d left outer join as l3 on ( c . dataid = l3 . id and c . version = l3 . vernum and l3 . attrid = 3 and l3 . defid = 325616 ) llattrdata left outer join as l4 on ( c . dataid = l4 . id and c . version = l4 . vernum and l4 . attrid = 2 and l4 . defid = 325616 )