Sql-server – String manipulation in Recursive CTE

cterecursivesql server

I am trying to make something like the following work:

WITH results(n, string ) AS (
            SELECT 1,'lol'
            UNION ALL
            SELECT n+1, string + ' lol'
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

But SQL doesn't seem to recognize the string concatenation in the second column and returns the error:

Types don't match between the anchor and the recursive part in column "string" of recursive query "results".

My desired output would be something like

1, lol

2, lol lol

3, lol lol lol

and so on

Best Answer

You can cast it to varchar(2000) or varchar(max) depending on your needs so both should have the same data type and size
for value 1, it using an integer/int data type
From BOL related to CTE ,

The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

WITH results(n, string ) AS (
            SELECT 1,CAST('lol' as varchar(2000))
            UNION ALL
            SELECT n+1, CAST(string + ' lol' as varchar(2000))
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

you can see the data type by using SQL_VARIANT_PROPERTY

WITH results(n, string, expressionType1, expressionType2) AS (
            SELECT 1,cast('lol' as varchar(2000)),
                    SQL_VARIANT_PROPERTY(1, 'BaseType'),
                    SQL_VARIANT_PROPERTY(cast('lol' as varchar(2000)), 'MaxLength')
            UNION ALL
            SELECT n+1, cast(string + ' lol' as varchar(2000)) ,
                SQL_VARIANT_PROPERTY(n+1, 'BaseType'),
                SQL_VARIANT_PROPERTY(cast(string + ' lol' as varchar(2000)), 'MaxLength')
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

output:

n           string               expressionType1 expressionType2
1           lol                  int             2000
2           lol lol              int             2000
3           lol lol lol          int             2000
4           lol lol lol lol      int             2000
5           lol lol lol lol lol  int             2000
6           lol lol lol lol lol  int             2000

Data type for value/expression 1 can be checked by:

select SQL_VARIANT_PROPERTY(1,'basetype')
int

Related Solutions

PostgreSQL tree structure and recursive CTE optimization

If you really have to modify these data rarely, then you can simply store the result of the CTE in a table, and run queries against this table. You can define indexes based on your typical queries.
Then TRUNCATE and repopulate (and ANALYZE) as necessary.

On the other hand, if you can put the CTE in separate stored procedures rather than a view, you can easily put your conditions in the CTE part rather then the final SELECT (which is basically what you do querying against tree_view_1), so that much less rows will be involved in the recursion. From the query plan it looks like that PostgreSQL estimates row numbers based on some far-from-true assumptions, probably producing suboptimal plans - this effect can be reduced somewhat with the SP solution.

EDIT I may miss something, but just noticed that in the non-recursive term you don't filter the rows. Possibly you want to include only root nodes there (WHERE parent_id IS NULL) - I'd expect much less rows and recursions this way.

EDIT 2 AS it slowly became clear for me from the comments, I misthought the recursion in the original question going the other way. Here I mean starting from the root nodes and going deeper in the recursion.

Mysql – Recursive Query in MySQL using stored proceedure and CURSOR

So the three questions I'm hoping someone can answer are:

1.Should I be using a CURSOR here or is there an alternative that will get the same recursive result?

No, you should not. The CURSOR keep being opened over and over. Thinking of the overhead make me cringe. Personally, I stay away from CURSORs.

2.How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?

3.What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work? I believe this is because I can't use CALL inline but I'm not sure.

I would like to bail you out of this by suggesting something I learned from my college days when learning about data structures. You need to perform tree traversal using a queue. This allows you to start at a root and express all descendants of a tree.

The algorithm goes like this

STEP 01 : Start with an empty queue
STEP 02 : Dequeue Node From the Front of the Queue
STEP 03 : Enqueue All Children of the Latest Node
STEP 04 : Process Info From the Latest Node
STEP 05 : If the Queue is Not Empty, Go Back to STEP 02
STEP 06 : All Done

This allows you to traverse a recursive structure without using Programmatic Recursion. At this point, you are probably asking: How can I traverse a tree structure without recursion?

I wrote a post about how to script three Stored Procedures that can using a loop in a single CALL that will traverse a table with nodes and its parent in a table:

GetParentIDByID
GetAncestry
GetFamilyTree

The post is Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011). It contains the Stored Procedures already written that will traverse the following table structure:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| parent_id  | int(11)      | YES  |     | NULL    |                | 
| name       | varchar(255) | YES  |     | NULL    |                | 
| notes      | text         | YES  |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+

Please read the code carefully and apply the principles.

Give it a Try !!!

Best Answer

Related Solutions

PostgreSQL tree structure and recursive CTE optimization

Mysql – Recursive Query in MySQL using stored proceedure and CURSOR

Related Question