mardi 27 janvier 2015

SQLITE Recursive Query

I've found an interesting recursive query here (http://ift.tt/1uYbeIn) and I adapted to my needs, but is not working.


Here's the code:



WITH cte AS (
SELECT 0 AS lvl, collectionID, collectionName, parentCollectionID, CAST(collectionID AS VARCHAR(128)) AS Sort
FROM collections WHERE parentCollectionID IS NULL
UNION ALL
SELECT p.lvl + 1, c.collectionID, c.collectionName, c.parentCollectionID, CAST(p.Sort + '/' + CAST(c.collectionID AS VARCHAR) AS VARCHAR(128))
FROM collections c
INNER JOIN cte p ON p.collectionID = c.parentCollectionID
)
SELECT
collectionID,
SPACE(lvl * 4) + collectionName AS collectionName,
Sort,
parentCollectionID
FROM cte ORDER BY Sort;


I receive this message: "Kernel error: near "WITH": syntax error"


Please, what I have to do to fix it? I'm using SQLIte 3.8.2


Aucun commentaire:

Enregistrer un commentaire