Re: Selecting the earliest admission

simpsonj_at_imsweb.com wrote:
> 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?

Put the procedures in a package, which quite frankly, is where all procedures belong. Declare the variables in the package header.

Be sure you understand how variables work in packages and use PRAGMA SERIALLY_REUSABLE if need be. You can find a demo of this functionality in Morgan's Library at www.psoug.org.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)