Recursive CTE's

[Manoj Chaurasia]

Is there a way to create the effect of a Recursive CTE in CIS 7.04

 

Based on how With is defined in the reference manual, this functions as a temporary table rather than how it would work in something like SQL Server. There also doesn't seem to be an Oracle equivalent of Connect By in the reference manual either.

[Frederick Wright]

Aaron, I've encountered this problem as well, it seems to be a quirk of how CTEs are implemented in CIS SQL. In my case I was able to get around it relatively easily by writing a packaged query against MS SQL Server, since all of the data sources involved were on a SQL instance, this allowed me to use the native SQL there.

Before I opted to use the packaged query approach, I had seriously considered writing a CIS procedure to do this work, since it seems to support flow-control statements. But I'm still a novice with the CIS SQL dialect.

Frederick Wright

Eaton Vance, Boston, MA

[Manoj Chaurasia]

When writing recursive procedures, one thing to be aware of is that there's a configuration parameter on the server that limits the maximum recursion depth of any set of procedures. The default limit is 30 levels, though we generally recommend increasing this to 100 levels.

The property is Server / Configuration / Transactions / Maximum Request Depth.

Please see the Whitepaper titled Configuring the Cisco Information Server for details.

[Manoj Chaurasia]

Matthele, could you provide an example of how to write a recursiveCTE within DV I've been able to create a CTE, but nothing that is self referential. I'm trying to create something like this:

 

WITH paths AS ( SELECT EmployeeID, CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath FROM EmployeeHierarchyWide WHERE ManagerID IS NULL

UNION ALL

SELECT ehw.EmployeeID, CONVERT(VARCHAR(900), CONCAT(p.FullPath, ehw.EmployeeID, '.')) FROM paths AS p INNER JOIN EmployeeHierarchyWide AS ehw ON ehw.ManagerID = p.EmployeeID ) SELECT * FROM paths

[Manoj Chaurasia]

Hi Aaron,

Sorry for the delay, I don't believe we currently support recursive CTEs.

For your example, I'd implement three views in CIS, one for each side of the UNION ALL and then a third view to do the UNION ALL between the two data sets.

My experience is that this gives you two things:

The CTE wouldn't, one is that the code is a bit more modular, so you could reuse either side of the union if you need it for another use case

Optimization of SQL tends to get tricky as your CTE's get more complex. Breaking the sub parts into different views tends to make it easier to optimize SQL or debug issues.

[Manoj Chaurasia]

Thank you for clarifying that Matthele. The challenge I run into is that I have a dynamicdepth of management that I can't realistically know ahead of time. Although I might know about 5 levels of management, in another part of the business there may be 15 levels.

 

Are there any plans in the future for this feature, or would it be possible to request it be added to the product in a later version

 

Is there any other way to dynamically step through levels in a unary table

[Manoj Chaurasia]

Hi Aaron,

You would need to recursive using a SQL script procedure to traverse the hierarchy.

I'm not certain about plans to implement recursive CTEs in the future, but you can always submit a feature request by opening a ticket with Cisco support.

[Juhan Leemet]

We are planning our upgrade to TDV 8.2; is there any update on recursive CTEs I am able to run them on DB2, but that same query gives errors in TDV SQL Scratchpad.