2

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?

Upendhar Singirikonda
  • 179
  • 14

3 Answers3

1

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)
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
1

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')))
Felypp Oliveira
  • 2,147
  • 1
  • 17
  • 17
0

SELECT ID, NAME, SAL FROM EMP WHERE ID IN (SELECT CUSFNC_GETEMPID('HIGH-RATED')AS ID FROM DUAL)

Deepak Kuletha
  • 1