5 Replies Latest reply: May 4, 2013 11:11 AM by Tubby RSS

    User Defined function in Where clause

    943841
      DB:- 11.2
      Input:- 'ACCOUNTING,SALES'
      Output:- ('ACCOUNTING','SALES')
      WITH T AS (select 'ACCOUNTING,SALES' str from dual)
      select '('||regexp_replace(str,'([[:alpha:]]+)','''\1''')||')' from t /*this works*/
      I've created a function to use this in a where clause
      create or replace function ss(dname varchar2)
      return varchar2 is
      begin
        RETURN '('||regexp_replace(dname,'([[:alpha:]]+)','''\1''')||')';
      end;  
      select ss('ACCOUNTING,SALES') from dual --this works
      But when I am using this function in a where clause result is not coming..any thing i am missing?
      select * from dept where dname in ss('ACCOUNTING,SALES') --no rows
        • 1. Re: User Defined function in Where clause
          Solomon Yakobson
          940838 wrote:
          Output:- ('ACCOUNTING','SALES')
          Wrong. ('ACCOUNTING','SALES') is a list of two strings 'ACCOUNTING' and 'SALES' while your function returns a single string '(''ACCOUNTING'',''SALES'')'.
          IN clause requires a comma-separated list of values while your function, again, returns just one value. So query is comparing 'SALES' whith '(''ACCOUNTING'',''SALES'')', not with ('ACCOUNTING','SALES') and obviously no match. What you are trying to do is called dynamic SQL. There are plenty examples on how to use it. But you don't need it. Use nested table or varray. I'll use Oracle supplied varray type sys.OdciVarchar2List:
          select  *
            from  dept
            where dname in (
                            select  *
                              from  table(sys.OdciVarchar2List('ACCOUNTING','SALES'))
                           )
          /
          
              DEPTNO DNAME          LOC
          ---------- -------------- -------------
                  10 ACCOUNTING     NEW YORK
                  30 SALES          CHICAGO
          
          SQL>
          SY.
          • 2. Re: User Defined function in Where clause
            Solomon Yakobson
            And if you are getting input as 'ACCOUNTING,SALES', use:
            select  *
              from  dept
              where dname in (
                              select  regexp_substr('ACCOUNTING,SALES','[^,]+',1,level)
                                from  dual
                                connect by level <= regexp_count('ACCOUNTING,SALES',',') + 1
                             )
            /
            
                DEPTNO DNAME          LOC
            ---------- -------------- -------------
                    10 ACCOUNTING     NEW YORK
                    30 SALES          CHICAGO
            
            SQL>
            SY.
            • 3. Re: User Defined function in Where clause
              943841
              Thank you..

              Edited by: 940838 on May 4, 2013 3:32 AM
              • 4. Re: User Defined function in Where clause
                Solomon Yakobson
                This might be simpler:
                select  *
                  from  dept
                  where ',' || 'ACCOUNTING,SALES' || ',' like '%,' || dname || ',%'
                /
                
                    DEPTNO DNAME          LOC
                ---------- -------------- -------------
                        10 ACCOUNTING     NEW YORK
                        30 SALES          CHICAGO
                
                SQL>
                SY.
                • 5. Re: User Defined function in Where clause
                  Tubby
                  Solomon's replies are all directly aimed at your problem so feel free to roll with any of them.

                  However, if you find you have to perform this type of operation frequently (I'm guessing you don't just do this in a single place) then you may want to look at

                  http://tkyte.blogspot.ca/2006/06/varying-in-lists.html

                  Which demonstrates a more encapsulated and reusable method you could use repeatedly and quite easily.

                  Cheers,