CTE in kdb recursive query

Question

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

Thomas Smyth · Accepted Answer · 2017-12-10 18:29:23Z

2

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

edited Dec 10, 2017 at 18:29

Thomas Smyth

5,6546 gold badges27 silver badges37 bronze badges

answered Feb 26, 2014 at 19:54

nightTrevors

6594 gold badges11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CTE in kdb recursive query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related