I wrote a Oracle SQL Function that will return EMP IDs in the following format.
Query: select CUSFNC_GETEMPID('HIGH-RATED') from dual;
Output: ('1436','1444','1234')
I want to use this Output in SQL Query as follows:
SELECT ID, NAME, SAL
FROM EMP
WHERE ID IN CUSFNC_GETEMPID('HIGH-RATED')
I am expecting this query returns records with IDs equal to values mentioned in the above query.
But it is not working as expected.
How to achieve this by modifying output of function or any other way?
You could use a join instead of a IN clause
SELECT ID, NAME, SAL
FROM EMP
inner join (
select CUSFNC_GETEMPID('HIGH-RATED') hrated from dual;
) t on t.hrated = emp.id
and could be that your emp.id is a number so you should convert to char
SELECT ID, NAME, SAL
FROM EMP
inner join (
select CUSFNC_GETEMPID('HIGH-RATED') hrated from dual;
) t on t.hrated = to_char(emp.id)
You can use a PIPELINED TABLE FUNCTION, like this:
CREATE FUNCTION CUSFNC_GETEMPID(param1 varchar2) RETURN DBMSOUTPUT_LINESARRAY PIPELINED AS
BEGIN
FOR a_row IN
(
-- YOUR SQL WHICH RETURNS ROWS WITH ID VALUES HERE...
SELECT id
FROM
(
SELECT '1' id, 'HIGH-RATED' col FROM dual UNION
SELECT '2' id, 'HIGH-RATED' col FROM dual UNION
SELECT '3' id, 'LOW-RATED' col FROM dual
)
WHERE col = param1 -- FILTER BASED ON INPUT PARAM...
)
LOOP
PIPE ROW(a_row.id); -- PUT THE ID COLUMN HERE...
END LOOP;
END;
/
And then call it in your SQL like this:
SELECT ID, NAME, SAL
FROM EMP
WHERE ID IN (SELECT column_value FROM TABLE(CUSFNC_GETEMPID('HIGH-RATED')))
SELECT ID, NAME, SAL FROM EMP WHERE ID IN (SELECT CUSFNC_GETEMPID('HIGH-RATED')AS ID FROM DUAL)
execute immediate). Instead you can use a table valued function rather than returning a string.