Hi

id parent_id
1001 null
1002 1001
1003 1001
1004 1002
1005 1002
1006 1003
1007 1003

If i say node_id=1002, i should get all the nodes under 1002. My output
should be 1004,1005 like this.

CREATE PROCEDURE REC_LIST (
SCAN_ID INTEGER)
RETURNS (
PARENT_ID INTEGER,
FOUND_ID INTEGER)
AS
begin
for select id, parent_id
from pages_node
where parent_id = :scan_id
into found_id, parent_id
do begin
suspend;
for select parent_id, found_id
from rec_list( :found_id )
into parent_id, found_id
do
suspend;
end
end


Please note, I modified your column names and your question. And I set you
root node to have parent NULL.
Anyway, adaption should be easy.

Hint: Don't store level_id. You have to change a lot of you move nodes and
this information is usually on no particular use.

Regards,
Steffen