4

I noted that Sybase SQL Anywhere supports them, but can't find any documentation on ASE also doing so.

If it doesn't, what would be my best option for designing a recursive query? In SQL Server 2008 I'd do it with a CTE, but if that's not available? A function perhaps?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
LJW
  • 2,378
  • 1
  • 21
  • 35

2 Answers2

0

Sybase ASE 12.5 (and also 15.0) doesn't support CTE.

You can use a inner query to solve your problem.

A simple CTE like this:

WITH Sales_CTE (Folders_Id)
AS
-- Define the CTE query.
(
    SELECT Folders_Id FROM Folders
)
SELECT Folders_Id FROM Sales_CTE

is the same as this:

SELECT aux.Folders_Id
FROM (SELECT Folders_Id FROM Folders) aux

For a litle more info, check this!

aF.
  • 64,980
  • 43
  • 135
  • 198
-3

  1. Since 1984, the Standard, and Sybase, allowed for full recursion. We generally perform recursion in a stored proc, so that depth is controlled, infinite loops are avoided, and execution is faster than uncompiled SQL, etc.

    Stored procs have no limits to recursion, result set construction etc. Of course, defining the content of the brackets as a View would make it faster again (it is after all a real View, not one that we have to Materialise every time we need it).

    Point being, if you were used to this method (recursion in the server, a proc coded for recursion), as I am, there is no need for CTEs, with its new syntax; uncompiled speeds; temp tables; work tables; a cursor "walking" the hierarchy; all resulting in horrendous performance.

    A recursive proc reads only the data, and nothing but the data, and reads only those rows that qualify at each level of the recursion. It does not use a cursor. It does not "walk", it builds the hierarchy.

  2. A second option is to use Dynamic SQL. Simply construct the SELECTs, one per level of the hierarchy, and keep adding the UNIONs until you run out of levels; then execute.

You can use a function to provide the facility of a CTE, but do not do so. A function is intended for a different, column-oriented purpose, the code is subject to those constraints. It is scalar, good for constructing a column value. Stored procs and CTEs are row-oriented.

halfer
  • 19,824
  • 17
  • 99
  • 186
PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • 5
    @PerformanceDBA: The CTE's SQL Server are not the same as materialized views (nor are they the same as your example). While they can be used in the same manner as you post (though your code works equally well in SQL Server and does not require the use of a CTE), CTE's can also be self-referential, allowing for recursive queries that would otherwise be impossible. Since the OP is asking about recursion, I would suspect that this is why. For more information, see http://msdn.microsoft.com/en-us/library/ms186243.aspx – Adam Robinson Jan 28 '11 at 03:17
  • 3
    Materialized views are indexed views, and exist much like a table. This is an example of an inline [non-materialized] view, or derived table. Some call it a subquery. – OMG Ponies Jan 28 '11 at 04:12
  • @Adam. Thankyou for following me. There may be a problem with semantics here. The CTE overcomes limits that MS had, that Sybase never had. I have been using recursive queries for over 20 years, if you explain what you mean by "impossible", I can provide a more explicit answer. – PerformanceDBA Jan 28 '11 at 04:56
  • "Inline View" is the Oracle term (and Oracle is somewhat less than the Standard). I try to use Standard terms, in order to avoid confusing beginners who read these pages. The Materialised View can be *described* as a derived table. But it is not a subquery, that means something else (unless you stretch the concept of subquery to mean "anything in brackets" !). In technical subjects, we try to use terms that have specific meaning, related to the context, etc, rather than descriptive or colloquial terms. Never heard of "indexed views" so I can't comment on what the technical term is. Thanks ! – PerformanceDBA Jan 28 '11 at 05:02
  • 1
    @PerformanceDBA: By impossible, I was referring to purely recursive traversal of a tree structure represented by parent-child relations. While there are techniques for representing trees that don't require recursion, the ANSI SQL recursive query syntax that I'm familiar with (introduced in SQL-99, IIRC, but don't hold me to that) uses syntax that's very similar to (if not outright compatible with, save for the exclusion of the requirement of the `RECURSIVE` keyword) the CTE's that are present in several RDBM's, including ASA and SQL Server. Is there another syntax that I'm missing? – Adam Robinson Jan 28 '11 at 14:08
  • @Adam. I wasn't discussing syntax. I was answering OP's question. Recursion is no problem at all in Sybase (eg. exact example in the link you provided). We do not have CTEs or the RECURSIVE syntax (standard or not). I have access to the Enhancement Request list, I have not seen any request for it. We do recursion (as per CTE example, and more) without such syntax, and 100s or orgs have been doing it for 25 years, on Sybase & MS SQL & DB2. Without cursors. What you call "impossible without CTEs" is simply not correct. Open a new question. – PerformanceDBA Jan 29 '11 at 10:21
  • 1
    @PerformanceDBA: you cannot do recursion without CTEs. If you feel differently, please post an example. Also Sybase SQL Anywhere uses CTEs (which are standardized BTW) to do recursion, same as SQL server. Sybase ASE doesn't seem to support CTEs though, so no recursive queries. And you're confusing a lot of terms here. Materialized view, inline query etc. – Paul Groke Jan 16 '13 at 12:53
  • @Paul Groke. Yes, we can do RECURSIVE queries without CTEs, and we have been doing it for over 25 years. I cannot provide the Sybase code for a CTE because OP has not provided a CTE. CTEs are fairly recent. If you want to know how recursion is done in Sybase ASE, **OPEN A NEW QUESTION**. It is not appropriate to place the answer to your question under this question (read the heading). Confusion: I have made one small clarification re MS Materialised View to the answer. No idea what "inline query" is, I did not use the term. – PerformanceDBA Mar 12 '13 at 07:35
  • @PerformanceDBA: Your "a" is an inline query (or rather the query inside the brackets that defines "a" is the inline query). You called it materialized view, which is not correct. A materialized view is a view that's materialized, i.e. permanently stored on disk, so it doesn't have to be computed at runtime. MS calls materialized views "indexed views" btw. And as I already wrote: there is no recursion going on here. – Paul Groke Mar 12 '13 at 12:19
  • @PerformanceDBA: p.s.: yes, one can do recursion without CTEs. But not in a single query, without using a temp-table or at least cursor loops. That was what I meant - maybe my previous statement should have been more clear in that regard. Oracle also provides an alternative syntax (CONNECT BY), but that's non-standard and Sybase doesn't support it. Long story short: CTEs are by far the best performing option. [politician] There is no alternative. [/politician] The cost of re-compilation is minimal compared to the overhead of other solutions. – Paul Groke Mar 12 '13 at 12:29
  • 2
    @Paul Groke. (1) The technical term for your "inline query" is Derived Table. Check the SQL Spec and the Sybase manuals. (2) I accept that CTEs are easier to code, than a proc with Recursion. The benefit ends there. The fact (check the STATISTICS) is, the performance of a CTE is horrendous. No surprise: it has to construct temp tables, plus work tables, plus mappings, and then "walk" the hierarchy. A recursive proc reads the data directly, and only the data, it doesn't need temp anything, work anything, walk anything. – PerformanceDBA Apr 20 '15 at 10:57