Selecting the earliest admission

Hi everyone. I currently have a procedure that takes the parameters, v_case_id and v_fac_id, finds the earliest admission date and returns the admission_id of the earliest admission date. Here is the SQL from this procedure:

SELECT admission_id

    FROM

          (SELECT admission_id
                  FROM admission a1
                         JOIN case c1 ON ca.case_id = a1.case_id
                    WHERE
                        c1.case_id = v_case_id
                        AND a1.fac_id = v_fac_id
                    ORDER BY
                        a1.date_of_disch_yyyy,
                        a1.date_of_disch_mm,
                        a1.date_of_disch_dd)
    WHERE
                rownum = 1)

This procedure works fine, but my problem lies in that I want to use the funtionality of this procedure in the where clause of another in order to select the admission with the earliest discharge for each person. However, I am not able to use the actual procedure. Ex.

SELECT

• list of fields FROM
• tables including: admission a, case c WHERE
  • constraints, including: a.admission_id = admission_id with earliest discharge

By putting the logic from the procedure at the beginning of the posting into the where clause and replacing the parameters v_case_id and v_fac_id, with a.admission_id and c.case_id, I get an error because the inner select is too deeply nested to recognize these values. Does anyone have an idea of how I can get around this problem? Received on Wed Feb 22 2006 - 17:09:00 CST