Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

Recommended Posts

  • 2 months later...

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 years later...
×
×
  • Create New...