Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009

    Unanswered: Informix SELECT FIRST issue

    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
    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

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

  2. #2
    Join Date
    Aug 2005
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts