nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

CS10 LiveReport - Metadata

Jaswinder_Davies

Hello Colin, Thanks for your advice. We were able to write the SQL below, based on your table suggestions and we are very close to meeting our target except it is doing a cross join and giving us too many rows. Please could you suggest the best join for this type of query? What I am looking for are the values for all the metadata for a given category e.g. Landlord Services. “Select dt.Name,dt.DataID,dt.VersionNum,DA.ID,DA.DefID,CM.AttrName,CM.CatName,CM.CatID,DA.ValDate,DA.ValStr,DA.ValIntfrom OTCS.DTreedt right outerjoin OTCS.LLAttrDataDA on dt.DataID= DA.IDright outerjoin OTCS.CatRegionMapCM on DA.DefID= CM.CatIDwhere CM.CatNamelike 'Landlord S%'and DA.ID= 1131133” Your help is greatly appreciated. Thank youJaz---------------------------------Jaswinder DaviesI.T. System AdministratorBusiness Services GroupEaling CouncilPerceval House3rd Floor -Mulberry Area14-16 Uxbridge RoadEaling, LondonW5 2HLTel: 0208 825 8635 mailto: DaviesJa@ealing.gov.uk Disclaimer: This email and any attachments to it are intended solely for the person to whom it is addressed. It may contain confidential or sensitive material and should be handled accordingly.If you have received this email or any of the information in it in error, or if you are not the intended recipient, you must not disclose, distribute, copy or print any of it, and all copies must be deleted from your system. Please notify the sender immediately.Whilst we take reasonable steps to identify software viruses, any attachments to this email may contain viruses which our anti-virus software has failed to identify. No liability is accepted for such viruses, and we therefore recommend that you carry out yourown anti-virus checks before opening any attachments. Information contained in this e-mail may be subject to public disclosure under the Freedom of Information Act 2000 or the Environmental Information Regulations 2004.

**********************************************************************

Please consider the environment before printing this email.

The content of this email and any attachment transmitted within are
confidential and may be privileged. If you are not the intended recipient
and have received this email in error, please notify the sender and delete
this message along with any attachments immediately. Unauthorised usage,
disclosure, copying or forwarding of this email, its content and/or
any attachments is strictly forbidden.

This footnote also confirms that this email message has been swept by
Mimecast for the presence of computer viruses.

www.mimecast.com

**********************************************************************

116080215053102219.gif

Find more posts tagged with

Comments

eLink User

Here is a better step by step way of geting that

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=3498953&objAction=ArticleView&viewType=1

once you understand your schema you may choose to just use this info to write a simple join.

You are not joining the versionum of the category as well as the version number of the node (dtreecore dataid)

The schema appears like this

for a node dataid version

select * from llattrdata where id=6897190 and vernum=4 and attrid=51

ID,VERNUM,DEFID,DEFVERN,ATTRID,ATTRTYPE,CUSTOMID,ENTRYNUM,PARENTKEYID,KEYID,VALINT,VALREAL,VALDATE,VALSTR

6897190,4,585293,40,51,-1,0,1,1,,,,,

I can read the above as Node 6897190 in dtree at versionnum4 has a category dtree.dataid and its version is 40 the attribute id being( 51) something i randomly picked

and btw since I have a value in a long field use that

..... appnair appunair( Livelink Rocks )

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

On Tue, Aug 2, 2016 at 9:10 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

[Attachments exceeded pre-set size limits. Use the links provided to access]
CS10 LiveReport - Metadata Posted byDavies, JaswinderOn 08/02/2016 10:10 AM

Hello Colin,

Thanks for your advice.

We were able to write the SQL below, based on your table suggestions and we are very close to meeting our target except it is doing a cross join and giving us too many rows. Please could you suggest the best join for this type of query?

What I am looking for are the values for all the metadata for a given category e.g. Landlord Services.

“Select dt.Name,dt.DataID,dt.VersionNum,DA.ID,DA.DefID,CM.AttrName,CM.CatName,CM.CatID,
DA.ValDate,DA.ValStr,DA.ValInt
from OTCS.DTreedt right outerjoin OTCS.LLAttrDataDA on dt.DataID= DA.IDright outerjoin OTCS.CatRegionMapCM on DA.DefID= CM.CatID
where CM.CatNamelike 'Landlord S%'and DA.ID= 1131133”

Your help is greatly appreciated.

Thank you
Jaz
---------------------------------
Jaswinder Davies
I.T. System Administrator
Business Services Group
Ealing Council
Perceval House
3rd Floor -Mulberry Area
14-16 Uxbridge Road
Ealing, London
W5 2HL
Tel: 0208 825 8635

mailto: DaviesJa@ealing.gov.uk

Disclaimer: This email and any attachments to it are intended solely for the person to whom it is addressed. It may contain confidential or sensitive material and should be handled accordingly.If you have received this email or any of the information in it in error, or if you are not the intended recipient, you must not disclose, distribute, copy or print any of it, and all copies must be deleted from your system. Please notify the sender immediately.Whilst we take reasonable steps to identify software viruses, any attachments to this email may contain viruses which our anti-virus software has failed to identify. No liability is accepted for such viruses, and we therefore recommend that you carry out yourown anti-virus checks before opening any attachments. Information contained in this e-mail may be subject to public disclosure under the Freedom of Information Act 2000 or the Environmental Information Regulations 2004.

**********************************************************************

Please consider the environment before printing this email.

The content of this email and any attachment transmitted within are
confidential and may be privileged. If you are not the intended recipient
and have received this email in error, please notify the sender and delete
this message along with any attachments immediately. Unauthorised usage,
disclosure, copying or forwarding of this email, its content and/or
any attachments is strictly forbidden.

This footnote also confirms that this email message has been swept by
Mimecast for the presence of computer viruses.

www.mimecast.com

**********************************************************************

Attachment Links:
116080215053102219.gif(4 KB)
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

eLink User

Here is a better step by step way of geting that

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=3498953&objAction=ArticleView&viewType=1

once you understand your schema you may choose to just use this info to write a simple join.

You are not joining the versionum of the category as well as the version number of the node (dtreecore dataid)

The schema appears like this

for a node dataid version

select * from llattrdata where id=6897190 and vernum=4 and attrid=51

ID,VERNUM,DEFID,DEFVERN,ATTRID,ATTRTYPE,CUSTOMID,ENTRYNUM,PARENTKEYID,KEYID,VALINT,VALREAL,VALDATE,VALSTR

6897190,4,585293,40,51,-1,0,1,1,,,,,

I can read the above as Node 6897190 in dtree at versionnum4 has a category dtree.dataid and its version is 40 the attribute id being( 51) something i randomly picked

and btw since I have a value in a long field use that

..... appnair appunair( Livelink Rocks )

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

On Tue, Aug 2, 2016 at 9:10 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

[Attachments exceeded pre-set size limits. Use the links provided to access]
CS10 LiveReport - Metadata Posted byDavies, JaswinderOn 08/02/2016 10:10 AM

Hello Colin,

Thanks for your advice.

We were able to write the SQL below, based on your table suggestions and we are very close to meeting our target except it is doing a cross join and giving us too many rows. Please could you suggest the best join for this type of query?

What I am looking for are the values for all the metadata for a given category e.g. Landlord Services.

“Select dt.Name,dt.DataID,dt.VersionNum,DA.ID,DA.DefID,CM.AttrName,CM.CatName,CM.CatID,
DA.ValDate,DA.ValStr,DA.ValInt
from OTCS.DTreedt right outerjoin OTCS.LLAttrDataDA on dt.DataID= DA.IDright outerjoin OTCS.CatRegionMapCM on DA.DefID= CM.CatID
where CM.CatNamelike 'Landlord S%'and DA.ID= 1131133”

Your help is greatly appreciated.

Thank you
Jaz
---------------------------------
Jaswinder Davies
I.T. System Administrator
Business Services Group
Ealing Council
Perceval House
3rd Floor -Mulberry Area
14-16 Uxbridge Road
Ealing, London
W5 2HL
Tel: 0208 825 8635

mailto: DaviesJa@ealing.gov.uk

Disclaimer: This email and any attachments to it are intended solely for the person to whom it is addressed. It may contain confidential or sensitive material and should be handled accordingly.If you have received this email or any of the information in it in error, or if you are not the intended recipient, you must not disclose, distribute, copy or print any of it, and all copies must be deleted from your system. Please notify the sender immediately.Whilst we take reasonable steps to identify software viruses, any attachments to this email may contain viruses which our anti-virus software has failed to identify. No liability is accepted for such viruses, and we therefore recommend that you carry out yourown anti-virus checks before opening any attachments. Information contained in this e-mail may be subject to public disclosure under the Freedom of Information Act 2000 or the Environmental Information Regulations 2004.

**********************************************************************

Please consider the environment before printing this email.

The content of this email and any attachment transmitted within are
confidential and may be privileged. If you are not the intended recipient
and have received this email in error, please notify the sender and delete
this message along with any attachments immediately. Unauthorised usage,
disclosure, copying or forwarding of this email, its content and/or
any attachments is strictly forbidden.

This footnote also confirms that this email message has been swept by
Mimecast for the presence of computer viruses.

www.mimecast.com

**********************************************************************

Attachment Links:
116080215053102219.gif(4 KB)
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Colin_Schmidt

My apologies for being a crazy-man in my queries. This is one I wrote a while back that separates and identifies more clearly the type of data (again, I’m a nut-case for detail and info I “need” to know). You can eliminate what you don’t want after you paste it into Notepad and look it over.

Sometimes it’s better to have very terse code to make it actually FINISH and not use up the DB Tempspace. This is minor, though, and will finish fairly quickly.

select dt1.parentid, dt3.name "Parent Folder", dt1.dataid, dt1.name "Object", llad1.vernum "Version", llad1.valstr "Category", llad1.defid, NVL(crm.attrname, llad2.valstr) "Attribute name", llad2.attrid "AttrID",

case

when llad2.attrtype=-18 then 'Category: ['||llad2.valstr||']'

when llad2.attrtype=-7 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valdate, 'Mon-dd-yyyy')

when llad2.attrtype=-4 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valreal)

when llad2.attrtype=-1 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr

when llad2.attrtype=2 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valint)

when llad2.attrtype=5 and llad2.valint=0 then '{'||to_char(llad2.attrtype)||'}'||'False'

when llad2.attrtype=5 and llad2.valint=1 then '{'||to_char(llad2.attrtype)||'}'||'True'

when llad2.attrtype=5 and llad2.valint is NULL then '{'||to_char(llad2.attrtype)||'}'||'n/a'

when llad2.attrtype=10 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr

when llad2.attrtype=11 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr

when llad2.attrtype=12 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valint)

when llad2.attrtype=14 then '{'||to_char(llad2.attrtype)||'} User ['||to_char(llad2.valint)||' ]'||k.name

when llad2.attrtype=10013 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr

when llad2.attrtype=10131 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr

when llad2.attrtype in (40006, 40007, 40008, 40009) then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.vallong)

else 'Other AttrType ['||to_char(llad2.attrtype)||']' end AttributeValue

from llattrdata llad1

join dtree dt1 on llad1.id = dt1.dataid and llad1.vernum = dt1.versionnum /* Object */

left outer join dtree dt2 on llad1.defid = dt2.dataid

left outer join dtree dt3 on dt1.parentid = dt3.dataid /* Parent record for the Name */

left outer join llattrdata llad2 on llad1.id = llad2.id and dt2.dataid = llad2.defid and llad1.vernum = llad2.vernum

join catregionmap crm on crm.catid = llad2.defid and crm.regionname = 'Attr_'||llad2.defid||'_'||llad2.attrid /* Get the title of the Attribute */

left outer join kuaf k on llad2.valint = k.id /* For AttryType = 14, a UserID is used */

/* Get the LLATTRDATA record for the latest version of the document/object */

where llad1.id = %1 and llad1.attrtype = -18 and llad1.parentkeyid = -1 and llad1.vernum = (select dt.versionnum from dtree dt where dt.dataid = %2)

order by dt1.name, llad2.defid, llad2.attrid

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Knowledgelink@capitalone.com

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, August 02, 2016 10:11 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: CS10 LiveReport - Metadata

[Attachments exceeded pre-set size limits. Use the links provided to access]

CS10 LiveReport - Metadata

Posted by Davies, Jaswinder On 08/02/2016 10:10 AM

Hello Colin,

Thanks for your advice.

We were able to write the SQL below, based on your table suggestions and we are very close to meeting our target except it is doing a cross join and giving us too many rows. Please could you suggest the best join for this type of query?

What I am looking for are the values for all the metadata for a given category e.g. Landlord Services.

“Select dt.Name,dt.DataID,dt.VersionNum,DA.ID,DA.DefID,CM.AttrName,CM.CatName,CM.CatID,

DA.ValDate,DA.ValStr,DA.ValInt

from OTCS.DTree dt rightouterjoin OTCS.LLAttrData DA on dt.DataID= DA.IDrightouterjoin OTCS.CatRegionMap CM on DA.DefID= CM.CatID

where CM.CatNamelike'Landlord S%'and DA.ID= 1131133”

Your help is greatly appreciated.

Thank you

Jaz

---------------------------------

Jaswinder Davies

I.T. System Administrator

Business Services Group

Ealing Council

Perceval House

3rd Floor -Mulberry Area

14-16 Uxbridge Road

Ealing, London

W5 2HL

Tel: 0208 825 8635

mailto:DaviesJa@ealing.gov.uk

Disclaimer: This email and any attachments to it are intended solely for the person to whom it is addressed. It may contain confidential or sensitive material and should be handled accordingly. If you have received this email or any of the information in it in error, or if you are not the intended recipient, you must not disclose, distribute, copy or print any of it, and all copies must be deleted from your system. Please notify the sender immediately. Whilst we take reasonable steps to identify software viruses, any attachments to this email may contain viruses which our anti-virus software has failed to identify. No liability is accepted for such viruses, and we therefore recommend that you carry out your own anti-virus checks before opening any attachments. Information contained in this e-mail may be subject to public disclosure under the Freedom of Information Act 2000 or the Environmental Information Regulations 2004.

**********************************************************************

Please consider the environment before printing this email.

The content of this email and any attachment transmitted within are
confidential and may be privileged. If you are not the intended recipient
and have received this email in error, please notify the sender and delete
this message along with any attachments immediately. Unauthorised usage,
disclosure, copying or forwarding of this email, its content and/or
any attachments is strictly forbidden.

This footnote also confirms that this email message has been swept by
Mimecast for the presence of computer viruses.

www.mimecast.com

**********************************************************************

Attachment Links:

116080215053102219.gif (4 KB)

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

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.