Category - SET Multivalue attribute

I'm trying get data in a live report for the Multi value Set-attribute in a category, not able to get the result set, please see the attachment of "Set Attribute" info and my query. Any help would be greatly appreciated. And we don't have web reports,

Thanks so much!!

Find more posts tagged with

Comments

CBancroft

Llattrdata is keyed on ID (which is the dataid of the object the set category/attribute is applied to)

To get values from llattrdata you need to reference the ID colun of llattrdata using the dataid of the object you want the values for. I don’t see that happening in your query

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Monday, November 5, 2018 10:39 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Category - SET Multivalue attribute

Category - SET Multivalue attribute

Posted byneeraja.adluru@dcwater.com (Adluru, Neeraja) On 11/05/2018 10:31 AM

Thanks so much!!

Attachment Links:

Category- Set info.docx (63 KB)

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Neeraja Adluru

Thank you so much for the prompt reply.

I did have "dt.dataID=da.dataid" in my query ,and I'm getting result set, but i'm not getting the correct rows of the SET Attribute values, i'm getting more.

CBancroft

Suspect that you are getting all the attributes for the object ( set attributes and any others in the category)

Each attribute has an attrid value in the llattrdata table

To return the values from only specific attributes you would refine the query to return only values for category “whatever” or “object whatever” match a specific attrid

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Monday, November 5, 2018 1:21 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE Category - SET Multivalue attribute

RE Category - SET Multivalue attribute

Posted byneeraja.adluru@dcwater.com (Adluru, Neeraja) On 11/05/2018 01:20 PM

Thank you so much for the prompt reply.

I did have "dt.dataID=da.dataid" in my query ,and I'm getting result set, but i'm not getting the correct rows of the SET Attribute values, i'm getting more.

Attachment Links:

Result set.xlsx (10 KB)

Category- Set info.docx (64 KB)

[To post a comment, use the normal reply function]
Topic:	Category - SET Multivalue attribute
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Nizar Ghazal

You may want to figure out specifically what attribute it is, and play around in SSMS a bit, focusing on it and its values.

I find ?func=attributes.dump is a good way to go, it gives an immediate listing to work with.

Neeraja Adluru

my problem is not with attribute id and, i do have dump info with me, i just need to know how to pull the SET Attribute values in the result set if attributes have multiple rows...

Thanks

André_Roy

Hi Neeraja,

When you have a multi-valued SET in a category, there will be a separate row in the LLAttrData table for each attribute. If you do a simple SELECT * for the specific Node and Category (DefID) in question, you can see how all the data is stored. I would recommend focussing on a single DTree Node and Category to make sure you get the proper results. Then you can try to join with the ancestors and other Categories.

What is desirable in your situation? You could return a single row per Node and concatenate all the individual attributes in the SET... OR return multiple rows per Node, 1 for each multi-valued record.

Regards,
André Roy

Neeraja Adluru

Currently I'm focusing on one item with one category which has a SET got 4 attributes and it has 3 rows, but when I run the query it is returning 18 rows (instead of 3) though i used "distinct".

I have tried iwth the parentkey also ..it dind't work.

Thanks so much

André_Roy

Based on the query from the previously attached Word doc, you were joining dtreeancestors with 2 categories (DefID). That is why I suggested what I did. Maybe you could provide a copy of the new query where you see 18 rows?

I don’t think you need to really worry about parentkeyID because if you say WHERE AttrID=4, it will return all matching records. There is likely a problem with the join.

Even though you see 3 rows in the Content Server UI for the SET, each attribute value has a distinct row in LLAttrData. 4 attributes times 3 rows would be 12 rows in LLAttrData; if you did a simple join between DTree and LLAttrData for the specific DataID, DefID and 4 attribute ID’s.

sıɹɥɔ

Hi Neeraja,

Sets do add complexity to the query needed to return rows corresponding to those of the set. They often throw people off. (The use of “distinct” is typically never needed -- and can be a sign of desperation :-) The reason that you’re getting more set rows than expected is because you are (inadvertently) joining the database rows with each other. What’s needed is to distinguish the set rows from each other. You’re on the right track using parentkeyid but not all the way there yet.

As usual with SQL there are multiple approaches. One is to note that a set is itself an attribute with its own attrid value. Each row within a set is given a “keyid”; this value is found in the database row for that set “attribute”. So we can add another join to llattrdata to get the keyid value and use it for each row within the set. For example you have aliases to the set category in llattrdata as “C”, “D”, “E”, and “F”. You’d have something (simplified) like this:

Join llattrdata K on attrid = <setattrid>

Join llattrdata A on attrid = <attridA> and A.parentKeyId = K.keyID

Join llattrdata B on attrid = <attridB> and B.parentKeyId = K.keyID

Join llattrdata C on attrid = <attridC> and C.parentKeyId = K.keyID

Join llattrdata D on attrid = <attridD> and D.parentKeyId = K.keyID

etc.

We could also note that it doesn’t really matter what the keyid value is, as long as all the attributes in the set have the same parentkeyid value. E.g. you’d have:

Join llattrdata A on attrid = <attridA> and A.parentKeyId = B.parentKeyID and A.parentKeyId = C.parentKeyID and A.parentkeyid=D.parentkeyid

Join llattrdata B on attrid = <attridB> and A.parentKeyId = B.parentKeyID and A.parentKeyId = C.parentKeyID and A.parentkeyid=D.parentkeyid

Join llattrdata C on attrid = <attridC> and A.parentKeyId = B.parentKeyID and A.parentKeyId = C.parentKeyID and A.parentkeyid=D.parentkeyid

Join llattrdata D on attrid = <attridD> and A.parentKeyId = B.parentKeyID and A.parentKeyId = C.parentKeyID and A.parentkeyid=D.parentkeyid

etc.

The insights in the two above approaches can be merged. The short answer to your question, then, is to simply add the condition:

and A.parentKeyID=B.parentKeyID and B.parentKeyID=C.parentKeyID and C.parentKeyID=D.parentKeyID

That should be all you need. (It looks a little “magical”, which is why I thought I would derive the formula first.)

Hope this helps,

-chris

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, November 05, 2018 1:19 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Category - SET Multivalue attribute

Category - SET Multivalue attribute

Posted byneeraja.adluru@dcwater.com (Adluru, Neeraja) On 11/05/2018 04:15 PM

Currently I'm focusing on one item with one category which has a SET got 4 attributes and it has 3 rows, but when I run the query it is returning 18 rows (instead of 3) though i used "distinct".

I have tried iwth the parentkey also ..it dind't work.

Thanks so much

Neeraja Adluru

Hi Chris,

Yes, I tried with Parentkey join between the all llattrdata tables, it worked.

Apprecited it.

Thank you so much!!

Neeraja Adluru

Hi Chris,

Please see the attached query, it is working. We can write query in many ways as you mentioned.

So plesae let me know if it looks good to you.

Thanks a lot!!!!!!

CBancroft

That looks a lot better Neeraja

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Tuesday, November 6, 2018 8:12 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE Category - SET Multivalue attribute 2

RE Category - SET Multivalue attribute 2

Posted byneeraja.adluru@dcwater.com (Adluru, Neeraja) On 11/06/2018 08:05 AM

Hi Chris,

Please see the attached query, it is working. We can write query in many ways as you mentioned.

So plesae let me know if it looks good to you.

Thanks a lot!!!!!!

Attachment Links:

LLATTRQuery.docx (14 KB)

[To post a comment, use the normal reply function]
Topic:	Category - SET Multivalue attribute
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Colin_Schmidt

TO reduce the list, you have to set all necessary parameters:

DTREE.DATAID = LLATTRDATA.ID

DTREE.VERSIONNUM = LLATTRDATA.VERNUM

LLATTRDATA.DEFID = <CategoryID>

LLATTRDATA.ATTRID = <AttributeID>

LLATTRDATA.ATTRTYPE = 11

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Monday, November 5, 2018 11:54 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE Category - SET Multivalue attribute

RE RE Category - SET Multivalue attribute

Posted bycbancrof@opentext.com (Bancroft, Chris) On 11/05/2018 01:54 PM

Suspect that you are getting all the attributes for the object ( set attributes and any others in the category)

Each attribute has an attrid value in the llattrdata table

To return the values from only specific attributes you would refine the query to return only values for category “whatever” or “object whatever” match a specific attrid

RE Category - SET Multivalue attribute

Posted by neeraja.adluru@dcwater.com (Adluru, Neeraja) On 11/05/2018 01:20 PM

Thank you so much for the prompt reply.

I did have "dt.dataID=da.dataid" in my query ,and I'm getting result set, but i'm not getting the correct rows of the SET Attribute values, i'm getting more.

Attachment Links:

Result set.xlsx (10 KB)

Category- Set info.docx (64 KB)

[To post a comment, use the normal reply function]
Topic:	Category - SET Multivalue attribute
Forum:	Content Server LiveReports Forum
Content Server:	My Support

[To post a comment, use the normal reply function]
Topic:	Category - SET Multivalue attribute
Forum:	Content Server LiveReports Forum
Content Server:	My Support