WITH CLAUSE in Netezza

Question

Unable to use JOIN inside the WITH CLAUSE

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 
  -- Anchor Member (AM)
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 7
  UNION ALL

  -- Recursive Member (RM)
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE e order by e.empid

ERROR [42S02] ERROR: relation does not exist ADMIN.EMPCTE

Can some one please give alternate Solutions ..thanks

score 0 · Answer 1 · answered Apr 22 '15 at 17:54

0

The problem here is not the JOIN, but that your are referencing the CTE within its own definition.

Netezza does not currently (as of v7.2) support recursive CTE/WITH expressions.

answered Apr 22 '15 at 17:54

ScottMcG

3,867
2
12
21

Thanks for the reply ...any alternate solutions? – Sayeekrishnan Subramaniam Apr 22 '15 at 18:05
This sort of logic will be best implemented as a stored procedure. – ScottMcG Apr 26 '15 at 20:43

WITH CLAUSE in Netezza

1 Answers1