Netezza Recursive Query Alternative and Examples

A recursive query is a way to query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy. Netezza does not support either WITH RECURSIVE Clause or Using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Netezza Recursive Query Alternative with an working example.

Netezza Recursive Query Alternative

In the other RDBMS such as Teradata you can specify a recursive query by preceding a query with the WITH RECURSIVE clause or creating a view using the RECURSIVE clause in a CREATE VIEW statement.

Netezza does not currently support recursive CTEs, you have to use Netezza UNION ALL or inner join approach if you know the depth of the recursive query heirarchy.

Netezza Recursive Query Alternative Examples

Below is the example of Netezza recursive query using UNION ALL. To implement the Netezza recursive query create following table and populate data.

CREATE TABLE emp
(
EMPID INT NOT NULL,
ENAME VARCHAR(60),
TITLE VARCHAR(15),
MANAGER INT,
SALARY NUMERIC(8,4),
DEPTID INT
) 
DISTRIBUTE ON (EMPID);

-- Populate Employee table with below data

INSERT INTO emp VALUES (1111, 'BLACK', 'Engineer', 1013, 800, 22);
INSERT INTO EMP VALUES (2222, 'WHITE', 'Sale', 6666, 1600, 33);
INSERT INTO EMP VALUES (3333, 'WATCHMAN', 'Sale', 6666, 1250, 33);
INSERT INTO EMP VALUES (4444, 'RHODES', 'Manager', 9999, 2975, 22);
INSERT INTO EMP VALUES (5555, 'MARTIN', 'Sale', 6666, 1250, 33);
INSERT INTO EMP VALUES (6666, 'BLAKE', 'Manager', 9999, 2850, 33);
INSERT INTO EMP VALUES (7777, 'CLARK', 'Manager', 9999, 2450, 11);
INSERT INTO EMP VALUES (8888, 'SCOTT', 'ANALYST', 4444, 3300, 22);
INSERT INTO EMP VALUES (9999, 'QUEEN', 'VP', NULL, 5000, 11);
INSERT INTO EMP VALUES (1010, 'TURNER', 'Sale', 6666, 1500, 33);
INSERT INTO EMP VALUES (1011, 'ADAMS', 'Engineer', 8888, 1100, 22);
INSERT INTO EMP VALUES (1012, 'GILLI', 'Engineer', 6666, 950, 33);
INSERT INTO EMP VALUES (1013, 'FORD', 'ANALYST', 4444, 3300, 66);
INSERT INTO EMP VALUES (1014, 'STEPHEN', 'Engineer', 7777, 1330, 11);

-- Query to find out emplyee and manager heirarchy

SELECT EMPID,ENAME,Manager, 0 as Level FROM Employee Emp WHERE Manager IS NULL
UNION ALL
SELECT EMP.EMPID, EMP.ENAME, EMP.Manager, Level+1 as Level FROM Employee Emp
INNER JOIN (SELECT EMPID, ENAME,Manager, 0 as Level FROM Employee Emp WHERE Manager IS NULL) a ON Emp.Manager = a.EMPID
WHERE Emp.Manager IS NOT NULL
UNION ALL
select EMP.EMPID, EMP.ENAME, EMP.Manager, Level+1 as Level FROM Employee Emp
INNER JOIN (
SELECT EMP.EMPID, EMP.ENAME, EMP.Manager, Level+1 as Level FROM Employee Emp
INNER JOIN (SELECT EMPID, ENAME,Manager, 0 as Level FROM Employee Emp WHERE Manager IS NULL) a ON Emp.Manager = a.EMPID
WHERE Emp.Manager IS NOT NULL) b ON Emp.Manager = b.EMPID
WHERE Emp.Manager IS NOT NULL
UNION ALL
SELECT EMP.EMPID, EMP.ENAME, EMP.Manager, Level+1 as Level FROM Employee Emp
INNER JOIN (select EMP.EMPID, EMP.ENAME,EMP.Manager, Level+1 as Level FROM Employee Emp
INNER JOIN (
SELECT EMP.EMPID, EMP.ENAME, EMP.Manager, Level+1 as Level FROM Employee Emp
INNER JOIN (SELECT EMPID, ENAME,Manager, 0 as Level FROM Employee Emp WHERE Manager IS NULL) a ON Emp.Manager = a.EMPID
WHERE Emp.Manager IS NOT NULL) b ON Emp.Manager = b.EMPID
WHERE Emp.Manager IS NOT NULL) a ON Emp.Manager = a.EMPID WHERE Emp.Manager IS NOT NULL;

Below is the result of the query:

 EMPID | ENAME | MANAGER | LEVEL 
-------+----------+---------+------- 
 4444 | RHODES | 9999 | 1 
 6666 | BLAKE | 9999 | 1 
 2222 | WHITE | 6666 | 2 
 8888 | SCOTT | 4444 | 2 
 1010 | TURNER | 6666 | 2 
 1012 | GILLI | 6666 | 2 
 1014 | STEPHEN | 7777 | 2 
 1111 | BLACK | 1013 | 3 
 1011 | ADAMS | 8888 | 3 
 9999 | QUEEN | | 0 
 7777 | CLARK | 9999 | 1 
 3333 | WATCHMAN | 6666 | 2 
 5555 | MARTIN | 6666 | 2 
 1013 | FORD | 4444 | 2 
(14 rows)

Netezza Recursive Query using Stored Procedure

You can implement the recursive query using stored procedure as well.

CREATE TABLE employee_rec_test (employee_number INT ,manager_employee_number INT);

insert into employee_rec_test values( 801,NULL);
insert into employee_rec_test values( 1003,801);
insert into employee_rec_test values( 1019,801);
insert into employee_rec_test values( 1016,801);
insert into employee_rec_test values( 1008,1019);
insert into employee_rec_test values( 1006,1019);
insert into employee_rec_test values( 1014,1019);
insert into employee_rec_test values( 1011,1019);
insert into employee_rec_test values( 1010,1003);
insert into employee_rec_test values( 1001,1003);
insert into employee_rec_test values( 1004,1003);
insert into employee_rec_test values( 1012,1004);
insert into employee_rec_test values( 1002,1004);
insert into employee_rec_test values( 1015,1004);

Following is the stored procedure.

CREATE OR REPLACE PROCEDURE rec_test() RETURNS 
int LANGUAGE NZPLSQL AS 
BEGIN_PROC
DECLARE
df_cnt int := 1;
cnt int :=1;
a int;
tblnm varchar(50) := '';
tblnm1 varchar(50) := '';
qry varchar(2500) := '';

BEGIN

-- Create initial temp table
create temp table temp_tab0 as (SELECT employee_number from employee_rec_test where manager_employee_number = 801);

while (df_cnt != 0 )
loop
tblnm := 'temp_tab'||(cnt-1);
tblnm1 := 'temp_tab'||cnt;

execute immediate 'select * from ( select indirect.employee_number 
FROM '||quote_ident(tblnm)||' direct, employee_rec_test indirect 
WHERE direct.employee_number = indirect.manager_employee_number) a';
df_cnt := ROW_COUNT;
if(df_cnt!=0)
then
execute immediate 'create temp table '||quote_ident(tblnm1)||' as select indirect.employee_number 
FROM '||quote_ident(tblnm)||' direct, employee_rec_test indirect 
WHERE direct.employee_number = indirect.manager_employee_number';
end if; 
cnt = cnt + 1;
end loop;

for a in 0..(cnt-2)
loop
if a=0
then
qry := qry || 'select employee_number from temp_tab' || a;
else
qry := qry || ' union select employee_number from temp_tab' || a;
end if;
end loop;

execute immediate 'create table restultset as '||qry ;

RETURN 1;

END;
END_PROC;

Now execute query and check results.

drop table restultset;
call rec_test();

select * from restultset order by 1;

EMPLOYEE_NUMBER
1001
1002
1003
1004
1006
1008
1010
1011
1012
1014
1015
1016
1019

Read: