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; } } }

Object Count query conversion from Oracle to SQL Server

Baber_Amin

Hi,

I have a query which is written in Oracle. I am wondering if someone can convert it to SQL Server?

select count(*) from dtree connect by prior dataid = parentid start with dataid=%1;

Thanks in advance.

Find more posts tagged with

Comments

Greg Kellogg

SQL Server doesn't have a Connect By Prior function. You can code around it but I am not sure how. I think there are people in this forum that will be able to help you though. Are your trying to get a total count of all items under the %1 parent? Or are you trying to get totals for each container under the %1 parent?

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: Wednesday, September 28, 2016 6:19:38 PM
To: eLink Recipient
Subject: Object Count query conversion from Oracle to SQL Server

Object Count query conversion from Oracle to SQL Server

Posted by Amin, Baber On 09/28/2016 07:08 PM

Hi,I have a query which is written in Oracle. I am wondering if someone can convert it to SQL Server?select count(*) from dtree connect by prior dataid = parentid start with dataid=%1;Thanks in advance.

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

****** 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.

Greg Kellogg

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

Object Count query conversion from Oracle to SQL Server

Posted by Amin, Baber On 09/28/2016 07:08 PM

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

Chris Meyer

Try this:

select count(*) as count from DTree INNER JOIN DTreeAncestors at ON DTree.DataID = at.DataID AND at.AncestorID = %1

Baber_Amin

Hi Greg,
I am trying to get total number of objects under %1.

Hi Chris,
Thanks very much your query worked. I was just wondering if this "DTreeAncestors" table new in CS10? May be I am wrong I don't remember anything like that in 9.7.1.

Thanks,

Baber.

eLink User

dtreeancestors existed in 971 or even before and it was tied to logic in the recommender module.Several 971 customers would not maintain that table because it had performace problems.In CS10 and higher your livelink will not work without that.Even they have other tables like Dbrowseancestors etc.In the absence of Oracle's connect by people in SQLserver would always rely on a object called CTE Since Livelink happens to be in SQLserver for a lot of customers lots of OT/Community queries using CTE's can be found in the LR forum.I have book marked plenty(Look for Lindsay Davies's posts) of good CTE's in the KB if you can't find one I can find it. Nowadays with dtreeancestors your life of reporting can be written agnostic to table vendor because of that because both oracle and sqlserver will work with the dtreeancestors queries.

Always when working with a LL version get a schema document and look at the introduced changes from version to version.Perhaps this may be new to you as well .DTREE is a view and they renamed the old table dtreecore.

Glad you found a way .I am just sharing this info if you ever wanted to use a CTE.

..... 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 Thu, Sep 29, 2016 at 10:58 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Object Count query conversion from Oracle to SQL Server Posted byAmin, BaberOn 09/29/2016 11:57 AM

Hi Greg,
I am trying to get total number of objects under %1.Hi Chris,
Thanks very much your query worked. I was just wondering if this "DTreeAncestors" table new in CS10? May be I am wrong I don't remember anything like that in 9.7.1.Thanks,Baber.
[To post a comment, use the normal reply function]Topic:Object Count query conversion from Oracle to SQL ServerForum:Content Server LiveReports ForumContent Server:Knowledge Center CS16

Baber_Amin

Hi,

Thanks very much for this valuable information. I'll have a look on the CTE and let you know for help.

Baber_Amin

Can someone convert this to CTE?

select count(*) from tree
connect by prior
dataid = parentid
start with dataid=%1

Hans Stoop

On which CS version are you?

In general you can better use a query on DTreeAncesstors for the same result.

Hans

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Mittwoch, 7. Dezember 2016 19:15
To: eLink Recipient
Subject: Re Object Count query conversion from Oracle to SQL Server

Re Object Count query conversion from Oracle to SQL Server

Posted by Amin, Baber On 12/07/2016 01:09 PM

Can someone convert this to CTE?

select count(*) from tree
connect by prior
dataid = parentid
start with dataid=%1

[To post a comment, use the normal reply function]
Topic:	Object Count query conversion from Oracle to SQL Server
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Baber_Amin

Hi,

I am on CS10.

Someone help me in building with dtreeancestors as below:

select name, dtc.DataID, parentid from DTreecore dtc, DTreeAncestors at where dtc.DataID = at.DataID AND at.AncestorID = %1;

But I want to see same with CTE. If someone can help that will be great.

Thanks.

Hans Stoop

It is the same query, you probably only need to get the case of the column name correctly,

I think it is

selectdt.Name, dt.DataID, dt.ParentIDfrom DTreedt, DTreeAncestors at wheredt.DataID= at.DataIDAND at.AncestorID= %1;

You should not use DTreeCore in queries, because it will also return deleted items.

Hans

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Donnerstag, 8. Dezember 2016 17:55
To: eLink Recipient
Subject: RE Re Object Count query conversion from Oracle to SQL Server

RE Re Object Count query conversion from Oracle to SQL Server

Posted by Amin, Baber On 12/08/2016 11:49 AM

Hi,

I am on CS10.

Someone help me in building with dtreeancestors as below:

selectname, dtc.DataID,parentid from DTreecore dtc,DTreeAncestors at where dtc.DataID= at.DataIDAND at.AncestorID= %1;

But I want to see same with CTE. If someone can help that will be great.

Thanks.

[To post a comment, use the normal reply function]
Topic:	Object Count query conversion from Oracle to SQL Server
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Colin_Schmidt

If it’s not working, it may be the “at” can not be used.

selectdt.Name, dt.DataID, dt.ParentID

fromDTreeAncestors dtan

join DTreedt on dtan.DataID= dt.DataID

wheredtan.AncestorID= %1

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Knowledgelink@capitalone.com

Internal: 433-8910

External: 804-284-8910

Cell: 918-904-9223

Don’t waste space with attachments – follow thesestepsto e-mail links to documents stored in Knowledgelink

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, December 08, 2016 1:23 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE Re Object Count query conversion from Oracle to SQL Server

RE RE Re Object Count query conversion from Oracle to SQL Server

Posted by Stoop, Hans On 12/08/2016 01:22 PM

It is the same query, you probably only need to get the case of the column name correctly,

I think it is

selectdt.Name, dt.DataID, dt.ParentIDfrom DTreedt, DTreeAncestors at wheredt.DataID= at.DataIDAND at.AncestorID= %1;

You should not use DTreeCore in queries, because it will also return deleted items.

Hans

RE Re Object Count query conversion from Oracle to SQL Server

Posted by Amin, Baber On 12/08/2016 11:49 AM

Hi,

I am on CS10.

Someone help me in building with dtreeancestors as below:

selectname, dtc.DataID,parentid from DTreecore dtc,DTreeAncestors at where dtc.DataID= at.DataIDAND at.AncestorID= %1;

But I want to see same with CTE. If someone can help that will be great.

Thanks.

[To post a comment, use the normal reply function]
Topic:	Object Count query conversion from Oracle to SQL Server
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Object Count query conversion from Oracle to SQL Server
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

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.