Hi,
I am trying to find all the emails in a folder that have a subtype =749. I would like to know what the SQL code is. Does anyone know?
Thank you
Jaz
For Oracle, that is very simple. If you want every Email under a Parent Folder (including Subfolders) try this:
Select dt.dataid, dt.name
From dtreeancestors dtan
Join dtree dt on dtan.dataid = dt.dataid and dt.subtype = 749
Where dtan.ancestorid = <ParentID>
Colin J. Schmidt
Knowledgelink Support
CapitalOne | Collaboration Technology
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, June 24, 2016 9:37 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: LiveReport to find all emails in a folder
LiveReport to find all emails in a folder
Posted by Davies, Jaswinder On 06/24/2016 09:29 AM
[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.
Colin,
You are better at SQL than I am so please take this as a quest for knowledge because that is what it is. Why involve dTreeAncestors? Wouldn't the following work just as well?
SELECT dataid, name
FROM dTree
WHERE subtype=749 and ParentID=%1
Since using a LiveReport, you can also set up the %1 as an Input Variable so that when the report is run, the user just needs to know the ID of the container they are searching.
I know you have a reason for involving the dTreeAncestors and believe it is probably related to the indexing but am not sure what it adds over a single table query. Again, I am just trying to understand. Is it an efficiency thing?
The reason is that the PARENTID only is the parent of those immediately below it. If that’s all you want, then it’ll work. But using the DTREEANCESTORS will have every object clear under all sub-folders linked to that “high” parent and each sub-folder will have its link to all of its child objects.
If you have:
FolderA
<doc>
FolderB
FolderC
And used DTREE.PARENTID, you’d only get the 1st two docs. If you used DTREEANCESTORS, you’d get all of them on down to the lowest level because all subsequent objects will have an association to any higher parent.
ANCESTORID DATAID
FolderA <doc>
FolderA FolderB
FolderA FolderC
<doc>…etc
FolderB <doc>
FolderB FolderC
FolderB <doc> … etc.
FolderC <doc>
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, June 24, 2016 10:34 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Re RE LiveReport to find all emails in a folder
Re RE LiveReport to find all emails in a folder
Posted by Kellogg, Greg On 06/24/2016 10:33 AM
Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200
the iQ Business Group
intelligence. applied.
www.IQBGinc.com
From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Friday, June 24, 2016 8:57:00 AMTo: eLink RecipientSubject: RE LiveReport to find all emails in a folder
RE LiveReport to find all emails in a folder
Posted by Schmidt, Colin On 06/24/2016 09:54 AM
Topic:
****** CONFIDENTIALITY NOTICE ****** This message and any attachments are confidential and for the sole use of the intended recipient. If you are not the intended recipient, any copying, forwarding, disclosure, distribution or use of any part of this message or its attachments is strictly prohibited and may be unlawful. If you believe that you received this email in error, please do not read it or any attachments thereto, and notify the sender immediately by email and then delete this message from your system. Thank you.
Thanks Colin. I know in Oracle they have the Connect to Prior that does some of this folder walking. I was unaware we could do it with dTreeAncestors. Great help - thanks again.
Re RE Re RE LiveReport to find all emails in a folder Posted byKellogg, GregOn 06/24/2016 11:51 AM Thanks Colin. I know in Oracle they have the Connect to Prior that does some of this folder walking. I was unaware we could do it with dTreeAncestors. Great help - thanks again.Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200the iQ Business Groupintelligence. applied.www.IQBGinc.com
That can be used to get the path, as well, but I’m not sure it would obtain ALL child objects. I think it would only retrieve based on the scope of “all object with PARENTID = ###”. If you happen to get into SQL Developer or some DB tool and open the DTREECORE, put in “PARENTID = ###” of a folder you know has sub-folders and see what you get….only those immediately below.
I’m no Oracle expert by any means. There are others who are more authoritative. I’m just a crazy data-miner.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, June 24, 2016 11:53 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Re RE Re RE LiveReport to find all emails in a folder
Re RE Re RE LiveReport to find all emails in a folder
Posted by Kellogg, Greg On 06/24/2016 11:51 AM
From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Friday, June 24, 2016 10:36:10 AMTo: eLink RecipientSubject: RE Re RE LiveReport to find all emails in a folder
RE Re RE LiveReport to find all emails in a folder
Posted by Schmidt, Colin On 06/24/2016 11:35 AM
There is no breadcrumb in the table. Not even a dry brownie. To find a parent, you could find that easily enough by putting the DATAID into the DATAID field and see what ANCESTORIDs show, but then which one is the immediate parent? You’d have to look at each Parent ID to see which group it is NOT in to know that that one is a child. Any further analysis might produce a tumor in my brain(I sit on it most of the day and still it’s not flat!). Difficult, I think and too much to figure out, but someone may have. But with the built-in Oracle functions, I don’t think it’s worth the effort.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, June 24, 2016 12:22 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Re Re RE Re RE LiveReport to find all emails in a folder
Re Re RE Re RE LiveReport to find all emails in a folder
Posted by eLink On 06/24/2016 12:20 PM
Dtreeancestors,dbrowseancestors is all OT's way of writing SQLindependent of database vendor,now that Hana & Postgres also came into the fray.
If they had put the name also as a column perhaps we could get the hierarchy also ,I dont know perhaps Colin might know.You could probably join that to dtree and pick up the bread crumb.Just a thought:)
.....appuappnairappunair
Well, if I called the wrong number, why did you answer the phone?James Thurber, New Yorker cartoon caption, June 5, 1937
On Fri, Jun 24, 2016 at 10:52 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:
Re RE Re RE LiveReport to find all emails in a folder Posted by Kellogg, Greg On 06/24/2016 11:51 AM Thanks Colin. I know in Oracle they have the Connect to Prior that does some of this folder walking. I was unaware we could do it with dTreeAncestors. Great help - thanks again. Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200the iQ Business Groupintelligence. applied.www.IQBGinc.comFrom: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Friday, June 24, 2016 10:36:10 AMTo: eLink RecipientSubject: RE Re RE LiveReport to find all emails in a folder RE Re RE LiveReport to find all emails in a folder Posted by Schmidt, Colin On 06/24/2016 11:35 AM The reason is that the PARENTID only is the parent of those immediately below it. If that’s all you want, then it’ll work. But using the DTREEANCESTORS will have every object clear under all sub-folders linked to that “high” parent and each sub-folder will have its link to all of its child objects. If you have: FolderA <doc> <doc> FolderB <doc> FolderC <doc> <doc> <doc> And used DTREE.PARENTID, you’d only get the 1st two docs. If you used DTREEANCESTORS, you’d get all of them on down to the lowest level because all subsequent objects will have an association to any higher parent. ANCESTORID DATAIDFolderA <doc>FolderA <doc>FolderA FolderBFolderA <doc>FolderA FolderC <doc>…etcFolderB <doc>FolderB FolderCFolderB <doc>FolderB <doc> … etc.FolderC <doc>FolderC <doc>FolderC <doc> Colin J. SchmidtKnowledgelink SupportCapitalOne | Collaboration Technology From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, June 24, 2016 10:34 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Re RE LiveReport to find all emails in a folder Re RE LiveReport to find all emails in a folder Posted by Kellogg, Greg On 06/24/2016 10:33 AM Colin,You are better at SQL than I am so please take this as a quest for knowledge because that is what it is. Why involve dTreeAncestors? Wouldn't the following work just as well? SELECT dataid, nameFROM dTreeWHERE subtype=749 and ParentID=%1 Since using a LiveReport, you can also set up the %1 as an Input Variable so that when the report is run, the user just needs to know the ID of the container they are searching. I know you have a reason for involving the dTreeAncestors and believe it is probably related to the indexing but am not sure what it adds over a single table query. Again, I am just trying to understand. Is it an efficiency thing? Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200the iQ Business Groupintelligence. applied.www.IQBGinc.comFrom: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Friday, June 24, 2016 8:57:00 AMTo: eLink RecipientSubject: RE LiveReport to find all emails in a folder RE LiveReport to find all emails in a folder Posted by Schmidt, Colin On 06/24/2016 09:54 AM For Oracle, that is very simple. If you want every Email under a Parent Folder (including Subfolders) try this: Select dt.dataid,dt.name From dtreeancestors dtanJoin dtree dt on dtan.dataid = dt.dataid and dt.subtype = 749Where dtan.ancestorid = <ParentID> Colin J. SchmidtKnowledgelink SupportCapitalOne | Collaboration Technology From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, June 24, 2016 9:37 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: LiveReport to find all emails in a folder LiveReport to find all emails in a folder Posted by Davies, Jaswinder On 06/24/2016 09:29 AM Hi,I am trying to find all the emails in a folder that have a subtype =749. I would like to know what the SQL code is. Does anyone know?Thank youJaz[To post a comment, use the normal reply function]Forum:Content Server LiveReports ForumContent 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.[To post a comment, use the normal reply function]Topic:LiveReport to find all emails in a folder Forum:Content Server LiveReports ForumContent Server:Knowledge Center****** CONFIDENTIALITY NOTICE ****** This message and any attachments are confidential and for the sole use of the intended recipient. If you are not the intended recipient, any copying, forwarding, disclosure, distribution or use of any part of this message or its attachments is strictly prohibited and may be unlawful. If you believe that you received this email in error, please do not read it or any attachments thereto, and notify the sender immediately by email and then delete this message from your system. Thank you.[To post a comment, use the normal reply function]Topic:LiveReport to find all emails in a folder Forum:Content Server LiveReports ForumContent 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.[To post a comment, use the normal reply function]Topic: LiveReport to find all emails in a folderForum: Content Server LiveReports ForumContent Server: Knowledge Center****** CONFIDENTIALITY NOTICE ****** This message and any attachments are confidential and for the sole use of the intended recipient. If you are not the intended recipient, any copying, forwarding, disclosure, distribution or use of any part of this message or its attachments is strictly prohibited and may be unlawful. If you believe that you received this email in error, please do not read it or any attachments thereto, and notify the sender immediately by email and then delete this message from your system. Thank you. [To post a comment, use the normal reply function]Topic: LiveReport to find all emails in a folderForum: Content Server LiveReports ForumContent Server: Knowledge Center
Select dt.dataid,dt.name
Use DBrowseAncestors if you want ancestors that most closely resemble what you would see in the browse view.
You can join with a WebNodes_* view to get a particular multilingual view of the names of the containers.
