>Hi,
>
>I have a table with the following DDL:
>
>CREATE TABLE LOCALITY (
> ID INTEGER NOT NULL,
> PARENT_ID INTEGER,
> CT_NODETYPES_ID INTEGER,
> DESCRIPTION VARCHAR(30),);
>
>The foreign key: CT_NODETYPES_ID refers to a table CT_NODETYPES with the
>following potential values:
>
>ID DESCRIPTION
>1 Country
>2 Province
>3 Farm registration districts
>4 District Municipality
>5 Metropolitan Municipality
>6 Local Municipality
>
>Typical data for LOCALITY are:
>
>ID PARENT_ID CT_NODETYPES_ID DESCRIPTION
>1 1 South Africa
>2 1 2 CONTINENTAL SHELF SOUTH AFRICA
>3 1 2 EASTERN CAPE
>4 1 2 FREE STATE
>5 1 2 GAUTENG
>6 1 2 KWAZULU-NATAL
>7 1 2 LIMPOPO
>8 1 2 MPUMALANGA
>9 1 2 NORTH WEST
>10 1 2 NORTHERN CAPE
>11 1 2 WESTERN CAPE
>128 10 3 BARKLY WEST
>129 10 3 BRITSTOWN
>130 10 3 CALVINIA
>146 11 3 BEAUFORT WEST
>147 11 3 BELLVILLE
>148 11 3 BREDASDORP
>178 9 4 Bojanala
>179 7 4 Capricorn
>180 9 4 Central
>181 9 4 Bophirima
>182 5 5 Ekurhuleni
>183 5 5 Johannesburg
>184 5 6 Mogale
>185 7 4 Mopani
>186 7 4 Sekhukhune
>187 7 4 Vhembe
>188 7 4 Waterberg
>189 9 4 Southern
>190 5 5 Tshwane
>
>I then typically use (as a foreign key) a record from LOCALITY at the lowest
>level, e.g. ID = 148 as the locality (farm registration district) of an
>abandoned mine
>or ID = 184 as the locality (Local municipality) of a unsafe mine opening.
>
>When I display the data in a grid of a web application or in a column of a
>report, I also want to display the province (ID = 2 in table CT_NODETYPES)
>
>I also want to generate stats from the mine table, e.g.
>
>Select Province_Name, Mine_Name, Count(ID) as Total from V_MINES
>where Province_ID = 4
>Group by Province_Name, Mine_Name
>
>V_MINES is a view spanning several tables.
>
>The problem I have is that (in the MINES table) I do not store the ID of the
>provinces but I store the ID of the farm registration districts (a child node
>of the province node).
>
>In the table for unsafe mine openings, I store the ID of local, metropolitan
>or district municipality.
>
>My use, and knowledge, of recursive common table expressions (CTE) is limited
>to iterate downwards from a node in a tree-structure (used as foreign key in a
>table).
>
>Now what I'm actually want is to look up the parent at a certain level (or
>classified as a certain type) of a "lower" record (node) in a tree-structure.
>
>Surely there must be a way to solve this problem?
This is probably doable in a recursive query, Nols, but I guess it is simpler
(or quicker) with execute block (which very easily can be changed into a stored
procedure if you want to):
EXECUTE BLOCK RETURNS(Province_Name Varchar(255), Mine_Name VarChar(255), Total
Integer) AS
DECLARE VARIABLE CT_NODETYPES_ID INTEGER;
BEGIN
FOR SELECT CT_NODETYPES_ID, MINE_NAME, COUNT(ID) FROM V_MINES
WHERE PROVINCE_ID = 4
GROUP BY CT_NODETYPES_ID, MINE_NAME
INTO :CT_NODETYPES_ID, :MINE_NAME, :TOTAL DO
BEGIN
WHILE (CT_NODETYPES_ID <> 2 AND CT_NODETYPES_ID IS NOT NULL) DO
SELECT CT_NODETYPES_ID
FROM LOCALITY
WHERE ID = :CT_NODETYPES_ID
INTO :CT_NODETYPES_ID;
PROVINCE_NAME = NULL;
IF (CT_NODETYPES_ID = 2) THEN
SELECT DESCRIPTION
FROM LOCALITY
WHERE ID = :CT_NODETYPES_ID
INTO :PROVINCE_NAME;
SUSPEND;
END
END
If there's only maximum three levels of recursion, you may probably even do it
without any iteration or recursion, but I guess the number of levels is unknown.
HTH,
Set
