Re: BUG #2847: Bug with IN statement

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