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

Connect by prior help..

Mukesh_Giri

Hi Pros:

How can i query a SQL 2008 based DB system for getting files folder etc under a parent folder which is easily returned for a ORA based system using something like following query.

select * from Dtree where subtype = 144 CONNECT BY PRIOR dataid = parentid start with dataid =12345

Thanks

Find more posts tagged with

Comments

Appu_Nair

Search for queries based on dtreeancestors

For solutions or SQL server functions or CTE

Sent from my iPhone

On Oct 14, 2011, at 6:39 PM, "eLink Entry: Content Server Development Forum" <development@elinkkc.opentext.com> wrote:

Connect by prior help.. Posted by mukesh.giri@sce.com (Giri, Mukesh)On 2011/10/14 19:38

Hi Pros:How can i query a SQL 2008 based DB system for getting files folder etc under a parent folder which is easily returned for a ORA based system using something like following query.select * from Dtree where subtype = 144 CONNECT BY PRIOR dataid = parentid start with dataid =12345Thanks
[To post a comment, use the normal reply function]
Forum: Content Server Development Forum
Livelink Server: knowledge-wlweb01

JaredAllmond

As suggested you can use a SQL Server recursive Common Table Expression (CTE) to replicate Oracle’s CONNECT BY PRIOR behavior. Here’s an example.

--Define CTE

WITH Docs([ParentID], [DataID], [Name], [Level])

(

--Anchor member definition

SELECT p.[ParentID]

,p.[DataID]

,p.[Name]

,0 AS [Level]

FROM [LES].[dbo].[DTree] p

WHERE p.[DataID]= 12345 --Starting Folder

UNIONALL

--Recursive member definition

SELECT c.[ParentID]

,c.[DataID]

,c.[Name]

,[Level] + 1

FROM [LES].[dbo].[DTree] c

INNERJOIN Docs d ON d.[DataID]= c.[ParentID]

)

--Execute Query using CTE

SELECT [DataID], [Name], [Level]FROM Docs

Regards,

Jared

Jared Allmond

Strategic Business Partners

243 West Congress St suite 480

Detroit, MI 48226

jallmond@sbp-mi.com

Office: 313.285.9582

Mukesh_Giri

Hi Appu:

Thanks for the dtreeancestors idea.

CTE query i had constructed but it was not giving any results.

When this morning i created something like below query

Select Name from Dtree where dataid in (select dataid from DTreeAncestors where DTreeAncestors.AncestorID=1132015)

I was able to get the results and i will twik it more as per the requirement.

Thanks to both of you for help.

Cheers

Mukesh

Quoted anair@alitek.com on 10/17/2011 01:43 PM:

Search for queries based on dtreeancestors

For solutions or SQL server functions or CTE

Sent from my iPhone

On Oct 14, 2011, at 6:39 PM, "eLink Entry: Content Server Development Forum" <development@elinkkc.opentext.com> wrote:

Connect by prior help.. Posted by mukesh.giri@sce.com (Giri, Mukesh) On 2011/10/14 19:38

Hi Pros:

How can i query a SQL 2008 based DB system for getting files folder etc under a parent folder which is easily returned for a ORA based system using something like following query.

select * from Dtree where subtype = 144 CONNECT BY PRIOR dataid = parentid start with dataid =12345

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