Hi,

I have an ACCOMPLISHMENTS table which has a Foreign key to itself:

ID STATUS COPIED_ID ACCOMPLISHMENT_DATE
-- -------------- --------- -------------------
1 Approved 01-JAN-2000
2 Archived 1 01-JAN-2001
3 Pending Review 01-JAN-2005
4 Disapproved 2 01-JAN-2002
5 In Progress 4 01-JAN-2003


So, here, there is a hierarchy of records. For eg., ID 2 is the child of ID 1, ID 4 is the child of ID 2 and ID 5 is the child of ID 4. ID 3 is a standalone record without any child records:

Approved
Approved->Archived
Approved->Archived->Disapproved
Approved->Archived->Disapproved->In Progress
Pending Review

I want to query the latest and greatest always and display to the user thru a JSP. For eg., if I query this table without any parameters (query criteria), I should get:

ID STATUS
-- --------------
3 Pending Review
5 In Progress

This is so, because ID 3 has no child records and ID 5 is the last child in all that hierarchy of records. If I query with a query criteria saying I want to see only those records with status of "Approved", then it should show:

ID STATUS
-- --------
1 Approved

Or if I say I want to see all accomplishments between 01-JAN-1999 to 31-DEC-2002 then I should see only:

ID STATUS
-- -----------
4 Disapproved


Can anyone help?

Thanks
Al

Al,

Does the hierarchy have a maximum depth, or is it able to grow as deep as desired?

Beth

it can grow as deep as required

Hi!
I think you'd better write function

FUNCTION GET_DETAILS ( intID IN NUMBER) RETURN VARCHAR2
IS
   strReturnValue VARCHAR2(4000);
   intCurrID NUMBER;
   strCurrValue VARCHAR2(50);
BEGIN
    -- Init value
    strReturnValue :='';
    intCurrID := intID;
    -- Do loop
    WHILE NOT intCurrID IS NULL LOOP
        EXIT WHEN intCurrID IS NULL;
        SELECT COPIED_ID, STATUS INTO intCurrID, strCurrValue
        FROM ACCOMPLISHMENTS
        WHERE ID = intCurrID;
        IF LENGTH(strReturnValue) > 0 THEN
            strReturnValue := strCurrValue || '->' || strReturnValue;
        ELSE
            strReturnValue := strCurrValue;
        END IF;
    END LOOP;
    RETURN strReturnValue;
END;

Now! You write SQL

SELECT GET_DETAILS(ID)
from ACCOMPLISHMENTS

Good Luck!