Re: hierachal or tree type queries using OLWGS
> I can post an example if you need more information.
>
And here it is.
(It was just too late yesterday to do so)
There is also an optimized version by Jonathan Leffler, that I don't
have anymore
as I moved to a different PC.
It is assumed, that Rows without a parent have a NULL-Value in the
parent-key-
column.
Regards,
Roland
CREATE PROCEDURE "ev4prod".expand_di_tree(master_district INTEGER)
RETURNING INTEGER,INTEGER,CHAR(2),INTEGER,SMALLINT,CHAR(15),CHAR(60);
DEFINE dist_dik INTEGER;
DEFINE dist_cok INTEGER;
DEFINE dist_lkuerz CHAR(2);
DEFINE dist_m_dik INTEGER;
DEFINE dist_lev SMALLINT;
DEFINE dist_di_sh_n CHAR(15);
DEFINE dist_di_lo_n CHAR(60);
IF master_district IS NULL
THEN
FOREACH WITH HOLD
SELECT district.dik,
district.cok,
country.lkuerz,
district.m_dik,
district.lev,
district.di_sh_n,
district.di_lo_n
INTO dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
FROM district,country
WHERE district.cok=country.cok AND m_dik IS NULL
ORDER BY di_sh_n
RETURN dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
WITH RESUME;
FOREACH EXECUTE PROCEDURE expand_di_tree(dist_dik)
INTO dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
RETURN dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
WITH RESUME;
END FOREACH;
END FOREACH;
ELSE
FOREACH WITH HOLD
SELECT district.dik,
district.cok,
country.lkuerz,
district.m_dik,
district.lev,
district.di_sh_n,
district.di_lo_n
INTO dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
FROM district,country
WHERE district.cok=country.cok AND m_dik=master_district
ORDER BY di_sh_n
RETURN dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
WITH RESUME;
FOREACH EXECUTE PROCEDURE expand_di_tree(dist_dik)
INTO dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
RETURN dist_dik,
dist_cok,
dist_lkuerz,
dist_m_dik,
dist_lev,
dist_di_sh_n,
dist_di_lo_n
WITH RESUME;
END FOREACH;
END FOREACH;
END IF;
END PROCEDURE;