>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?
Hi Nols!
I don't quite get what you're trying to do, I'll guess, but feel free to
reformulate your problem if my guessing is wrong. My guess is that you're
trying to do, is to traverse two trees and join them together.
Maybe something like this will be appropriate:
/*-------------------------Bof query------------------------------*/
With Recursive
RecurseProgressType as
(
Select ID from Progress_Type
where ID = :ID_RootProgressType
Union all
Select TP.ID from Progress_Type TP
Inner join RecurseProgressType Parent on 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
inner join RecurseProgress as CR1 on Child.parent_ID = CR1.ID
)
Select * from RecurseProgress Pr
Inner join RecurseProgressType RPT on Pr.ProgressTypeID = RPT.ID
Where Pr.OldMines_ID = :ID
order by Pr.ID, RPT.ID
/*-------------------------Eof query------------------------------*/
HTH,
Set
