0

I want to use my result of function e.g. 'S500,S600,S700,S800' in a subquery in another script like: 

where dept_no in (my result of function)

So I want to convert my string result to be like this ('S500','S600','S700','S800').

I tried to do this with dynamic SQL but I can't get it to work.

mhmd_sami
  • 13
  • 2
  • Please [edit](http://stackoverflow.com/posts/41930234/edit) your question and add some code. Do you get any error message? – diiN__________ Jan 30 '17 at 06:41
  • could be use regexp_substr https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement to separate string into comma partitions – Álvaro Touzón Jan 30 '17 at 06:42
  • 1
    In short: you can't. But why don't you change your function to return a table? Then you can do: `where dept_no in (select dept_no from table(your_function())` –  Jan 30 '17 at 07:16
  • Possible duplicate of [Is there a function to split a string in PL/SQL?](http://stackoverflow.com/questions/3710589/is-there-a-function-to-split-a-string-in-pl-sql) – J. Chomel Jan 30 '17 at 10:04
  • Possible duplicate of about a million 'comma-separated list' questions: http://stackoverflow.com/search?q=%5Boracle%5D+comma-separated – William Robertson Jan 30 '17 at 11:04
  • 1
    Duplicate of the many solutions in the [SO Oracle Documentation](http://stackoverflow.com/documentation/oracle/1968/splitting-delimited-strings) pages. (Note: the highest voted documentation example is probably simplest to write but the least efficient as per the discussion in [this answer](http://stackoverflow.com/q/38371989/1509264)) – MT0 Jan 30 '17 at 11:06
  • @ÁlvaroTouzón big thanks for your suggest and it get the exact result that i want but using type table is recommended for performance issue – mhmd_sami Jan 31 '17 at 12:50

4 Answers4

0

Hope below snipet suffice your requirement.

Approach 1 -> More effective

--Create a table type of VARCHAR

CREATE OR REPLACE type string_table
IS
  TABLE OF VARCHAR2(100);

--Function to return tabl type

CREATE OR REPLACE
FUNCTION string_manipulate
  RETURN string_table
AS
  str_tab string_table;
BEGIN
  SELECT 's00'||level bulk collect INTO str_tab FROM dual CONNECT BY level < 10;
  RETURN str_tab;
end;

--Use function in the query

SELECT distinct 1
FROM
  (SELECT 's001' dn FROM dual
  UNION ALL
  SELECT 's002' dn FROM dual
  UNION ALL
  SELECT 's003' dn FROM dual
  UNION ALL
  SELECT 's004' dn FROM dual
  UNION ALL
  SELECT 's005' dn FROM dual
  UNION ALL
  SELECT 's006' dn FROM dual
  UNION ALL
  SELECT 's007' dn FROM dual
  UNION ALL
  SELECT 's008' dn FROM dual
  UNION ALL
  SELECT 's009' dn FROM dual
  )a
WHERE a.dn IN
  (SELECT * FROM TABLE(string_manipulate)
  );


--Approach 2
--Function to get output as mentioned.

CREATE OR REPLACE
  FUNCTION string_manipulate
    RETURN VARCHAR2
  AS
  BEGIN
    RETURN 'S2009,S2020,S2021';
  END;

-- Use function value in a query

SELECT 1
FROM dual
WHERE '''S2009'',''S2020'',''S2021''' = (''''
  ||REPLACE(string_manipulate,',',''',''')
  ||'''');
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25
  • The problem with this implementation is that you can end up with a big performance problem with larger data set. The optimizer will have no way to determine cardinalities. Along the lines of what @a_horse_with_no_name suggests, I'd be interested in how this list of strings is generated. Perhaps it could even be a join. – BobC Jan 30 '17 at 07:26
  • Yeaah i totally agree. I assumed the data volume to be on a lower side. Yes the better approcah will be returning a table type function. – Avrajit Roy Jan 30 '17 at 07:29
  • @AvrajitRoy big thanks for your idea of type table.. i tried many solutions but using type table is the best solution for performance – mhmd_sami Jan 31 '17 at 12:48
0

You need an iterator and text splitting by comma sign:

select empno,ename,sal,deptno
from emp
where empno in (
select to_number(
rtrim(
substr(emps,
instr(emps,',',1,iter.pos)+1,
instr(emps,',',1,iter.pos+1) -
instr(emps,',',1,iter.pos)),',')) emps
from (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,
(select rownum pos from emp) iter
where iter.pos <= ((length(csv.emps) -
length(replace(csv.emps,',')))/length(','))-1
)

But better rewrite your function to return cursor.

nilsman
  • 346
  • 1
  • 9
0

you can use collection:

SELECT *
  FROM YOUR_TABLE
 WHERE DEPT_NO IN (SELECT *
                     FROM TABLE (SPLIT ('S500,S600,S700,S800')))--splits text with comma, for other chars use split(text, split_char)

With usage of MEMBER OF

SELECT *
  FROM YOUR_TABLE
 WHERE DEPT_NO MEMBER OF SPLIT ('S500,S600,S700,S800')--splits text with comma, for other chars use split(text, split_char)

the split fuction is:

CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2 (32767);

CREATE OR REPLACE FUNCTION SPLIT (P_LIST VARCHAR2, P_DEL VARCHAR2 := ',')
   RETURN SPLIT_TBL
   PIPELINED
IS
   L_IDX     PLS_INTEGER;
   L_LIST    VARCHAR2 (32767) := P_LIST;
BEGIN
   LOOP
      L_IDX := INSTR (L_LIST, P_DEL);

      IF L_IDX > 0
      THEN
         PIPE ROW (SUBSTR (L_LIST, 1, L_IDX - 1));
         L_LIST := SUBSTR (L_LIST, L_IDX + LENGTH (P_DEL));
      ELSE
         PIPE ROW (L_LIST);
         EXIT;
      END IF;
   END LOOP;

   RETURN;
END SPLIT;
hmmftg
  • 1,274
  • 1
  • 18
  • 31
0
 FUNCTION GET_TS_EACH_DAY_DEPARTMENT (P_SER_NO     VARCHAR2,
                                   P_TS_DATE    DATE
                                   )
      RETURN STRING_TABLE     
   IS
     V_DEPT_NO       VARCHAR2 (4000);
     V_DEPT          VARCHAR2(4000);
     V_TABLE         STRING_TABLE:=STRING_TABLE();
     J NUMBER:=1;
   BEGIN
     for i in (select distinct ts_day dayy from WEB_TS_USER_LOCATIONS_V ) loop
     V_TABLE.EXTEND;
        V_TABLE(J):= WEB_TS_PKG.GET_TS_DAY_DEPARTMENT (P_SER_NO ,P_TS_DATE  , i.dayy );
         J:=J+1;
     end loop;
    RETURN V_TABLE;
   END GET_TS_EACH_DAY_DEPARTMENT;
mhmd_sami
  • 13
  • 2