Hi,
I have problems with using FIRST option in the select query. The query works correctly when ran individually but it fails when i use the same query in FOR EACH loop of the stored procedure. here is the sample of the code. Could someone please suggest me the right one to use instead of FIRST.

IF is_prsn_dvsn_id > 0 THEN
FOREACH
SELECT p.prsn_site_loc_id, p.prsn_dvsn_id, p.prsn_cmplx_id,
p.dio_site_loc_desc, p.housing_unit_flg, p.vld_flg
INTO os_site_loc_id, os_prsn_dvsn_id, oc_prsn_cmplx_id,
oc_site_loc_desc, oc_house_unit_flg, oc_vld_flg
FROM prsn_site_loc p
WHERE p.prsn_dvsn_id = is_prsn_dvsn_id
AND p.prsn_cmplx_id = ic_prsn_cmplx_id
ORDER BY p.dio_site_loc_desc

LET li_status = 0;
LET oc_cmplx_key = os_prsn_dvsn_id || "-" || oc_prsn_cmplx_id;


LET oc_fclty_id = NULL;
Select first 1 prsn_fclty_id
Into oc_fclty_id
From prsn_fclty
Where prsn_dvsn_id = is_prsn_dvsn_id
AND prsn_cmplx_id = ic_prsn_cmplx_id;
-----------------------------------------------

RETURN li_status, os_site_loc_id, os_prsn_dvsn_id,
oc_prsn_cmplx_id, oc_site_loc_desc, oc_house_unit_flg,
oc_vld_flg, oc_cmplx_key, oc_fclty_id-- smarkley 5/8/08
WITH RESUME;
END FOREACH

Basically i wanted to retrieve only one record from the select statement returning multiple records..

Hi...
I don't think the problem is in the 'first' option.
I tried it within the foreach loop and it works!(funny thing is that documentation says it shouldnt work)

Try max(prsn_fclty_id) instead of first, if something happens, but I assume you will get the same error.

And btw. what is the error number you got?