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