On 12/21/06, Sandip <[EMAIL PROTECTED]> 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,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | yahoo }.com
