CODEWITHSUNDEEP
recursioncommon-table-expressionkdb+
Vikas
Vikas

Reputation: 138

CTE in kdb recursive query

Following is the CTE example in sql server. I have to do similar recursion in KDB. Is KDB support recursive queries or something close to it. Currently i can think of creating functions and hold the temporary data of each recursion into something...

USE AdventureWorks2012;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO

Upvotes: 1

Views: 620

Answers (1)

nightTrevors
nightTrevors

Reputation: 649

I'm not 100% sure what you are trying to do but two useful recursive keywords in kdb are

over

and 

scan

If you are only interested in the final result of the recursion you use over:

ex.

q){x+2*y} over 2 3 5 7
32

but if you want the output from each step, use scan:

ex.

q){x+2*y} scan 2 3 5 7
2 8 18 32

These are both examples from Jeff Borror's q for mortals. More here: http://code.kx.com/q/ref/control/#over

Upvotes: 2

Related Questions