Hi,
I created a recursive Sql query to lookup the nodes of a thread and where each
node is classified according to a tree-structure (the variable Progress_Type_ID
in the query which is linked to ID, the primary key of the tree-structure
Progress_Type).
The following SQL to do the recursive query:
/*--------------------------Bof query-------------------------*/
With Recursive
RecurseProgress as
(
Select ID, Parent_ID, OldMines_ID, Progress_Type_ID, Progress_Type_Parent_ID,
Progress_Type_Description,
Instance_Date, Progress, 1 as ALevel from V_OldMines_Progress
where Parent_ID is null
Union all
Select Child.ID, Child.parent_id, Child.OldMines_ID, Child.Progress_Type_ID,
Child.Progress_Type_Parent_ID,
Child.Progress_Type_Description, Child.Instance_Date, Child.Progress,
ALevel + 1 from V_OldMines_Progress Child
inner join RecurseProgress as CR1 on Child.parent_ID = CR1.ID
)
Select * from RecurseProgress Pr
Where Pr.OldMines_ID = :ID
order by Pr.ID
/*-------------------------Eof query------------------------------*/
If I execute this query, ID is requested as the parameter
Now I want to modify it because Progress_Type_ID is also the primary key of a
tree-structure and if I execute the modified query, it must ask for the two
parameters, the current ID for the view V_OldMines_Progress and the new
starting point of the primary key of table Progress_Type.
I tried the following and then it asks for two parameters:
ID_ROOTPRGRESSTYPE and ID (any value for ID_ROOTPRGRESSTYPE have the
same results):
/*-------------------------Bof query------------------------------*/
With Recursive
RecurseProgressType as
(
Select ID from Progress_Type
where ID = :ID_RootProgressType
Union all
Select TP.ID from Progress_Type TP, RecurseProgressType Parent
where TP.Parent_ID = Parent.id
)
,
RecurseProgress as
(
Select ID, Parent_ID, OldMines_ID, Progress_Type_ID, Progress_Type_Parent_ID,
Progress_Type_Description,
Instance_Date, Progress, 1 as ALevel from V_OldMines_Progress
where Parent_ID is null
Union all
Select Child.ID, Child.parent_id, Child.OldMines_ID, Child.Progress_Type_ID,
Child.Progress_Type_Parent_ID,
Child.Progress_Type_Description, Child.Instance_Date, Child.Progress,
ALevel + 1 from V_OldMines_Progress Child , RecurseProgressType
inner join RecurseProgress as CR1 on Child.parent_ID = CR1.ID
)
Select * from RecurseProgress Pr
Where Pr.OldMines_ID = :ID
order by Pr.ID
/*-------------------------Eof query------------------------------*/
Any idea what I did wrorg?
Regards,
Nols Smit
Council for Geoscience, South Africa
