Help converting Oracle LiveReport to SQL

I have the following Oracle LiveReport that takes in a folder id (user selects the folder) and it lists the contents of the folder and sub folders. It uses start with and connect by. I am now on an SQL system and cannot figure out the equivalents for start with and connect by so any help in converting this will be greatly appreciated. Full LiveReport is attached.

select lpad(' ', 4*(level -1) + 1, '--') || d.name "Name", d.gif, d.subtype from dtree d start with d.dataid = %1 connect by prior d.dataid = d.parentid

Thank You

Charis

LiveReport.rpt

Find more posts tagged with

Comments

CBancroft

Hi Charis,

You may find the information at these links helpful.

https://knowledge.opentext.com/knowledge/llisapi.dll/open/11725974

https://knowledge.opentext.com/go/4610762

and

https://knowledge.opentext.com/go/13448797

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, November 25, 2011 3:34 AM
To: eLink Recipient
Subject: Help converting Oracle LiveReport to SQL

Help converting Oracle LiveReport to SQL

Posted by ckelly@windowlogic.com.au (Kelly, Charis) On 11-25-2011 03:27

select lpad(' ', 4*(level -1) + 1, '--') || d.name "Name", d.gif, d.subtype from dtree d start with d.dataid = %1 connect by prior d.dataid = d.parentid

Thank You

Charis

Attachment Links:

LiveReport.rpt (1 KB)

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

Mukesh_Giri

Hi CHaris:

Easiest way to do so is by using dtreeancestors table or you can create a equivalent of Oracle Connect by stint using CTE in SQL DB.

See if the below query resolves your requirement and do let me know how it goes, if that is not sufficient i will get you more ..:)

Select count(*) from Dtree where dataid in (select dataid from DTreeAncestors where DTreeAncestors.AncestorID= 1132015 and subtype = 0)

Cheers,

Quoted ckelly@windowlogic.com.au on 2011-11-28 02:10:

select lpad(' ', 4*(level -1) + 1, '--') || d.name "Name", d.gif, d.subtype from dtree d start with d.dataid = %1 connect by prior d.dataid = d.parentid

Thank You

Charis

Kelly_Charis

Hi Mukesh;

Thank you that has been quite helpful any ideas how to achieve the tabbed over appearance of lpad in MSSQL? I did try a CTE which just made me shudder I do love the elegance of Oracle but find myself in MSSQL land these days :-)

Thanks,

Charis

Quoted mukesh.giri@sce.com on 2011-11-29 23:02:

Hi CHaris:

Easiest way to do so is by using dtreeancestors table or you can create a equivalent of Oracle Connect by stint using CTE in SQL DB.

See if the below query resolves your requirement and do let me know how it goes, if that is not sufficient i will get you more ..:)

Select count(*) from Dtree where dataid in (select dataid from DTreeAncestors where DTreeAncestors.AncestorID= 1132015 and subtype = 0)

Cheers,

Quoted ckelly@windowlogic.com.au on 2011-11-28 02:10:

select lpad(' ', 4*(level -1) + 1, '--') || d.name "Name", d.gif, d.subtype from dtree d start with d.dataid = %1 connect by prior d.dataid = d.parentid

Thank You

Charis