Re: BUG #2847: Bug with IN statement

["Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>]

On 12/21/06, Sandip <sandip(at)singapore(dot)com> wrote:

CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
  RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT          BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
                      AddInfo1, AddInfo2
FROM            T_PHASE
WHERE           (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
  LANGUAGE 'sql' VOLATILE;


select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record.

For this function call, the SELECT inside the function gets translated into

SELECT          ...
FROM            T_PHASE
WHERE           (USER_ID = 'sandip') AND (COMPANY_ID = 'oms') AND BOOK_NO IN ('1,4')

Notice the quotes around the list of values for $3. So you can see that the IN list contains just one value and not two as you might have expected. You'll need to do some trickery. Try the EXECUTE, perhaps that might help!!!
 

I tried to execute the SQL statement from the function

SELECT          BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
                      AddInfo1, AddInfo2
FROM            T_PHASE
WHERE           (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND
BOOK_NO IN (1,4)

----- This Works fine... returns 2 records.   What may be the problem?

Best regards,


-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com