6 Replies Latest reply: Feb 8, 2013 2:39 PM by JustinCave RSS

    In with lsttagg function

    choti
      How can i use IN clause with L_list variable from below code.
      Declare
      l_list varchar2(20000);
      BEGIN
      select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
      INTO L_LIST
      FROM   emp;
      insert into emp_target(dept_no,sal,ename)
      select dept_no,sal_ename
      where ename in (L_list);
      commit;
      End;
        • 1. Re: In with lsttagg function
          Solomon Yakobson
          Your code in nothing more but:
          insert
            into emp_target(dept_no,sal,ename)
            select  dept_no,
                    sal,
                    ename
              from  emp
              order by ename;
          SY.
          • 2. Re: In with lsttagg function
            Frank Kulash
            Hi,
            choti wrote:
            How can i use IN clause with L_list variable from below code.
            LISTAGG is good for making a big string with data from multiple rows. Such a string is no good for an IN sub-query; the multiple rows that you started with are much better.

            It sound like you want something like this:
            INSERT INTO emp_target 
                   (dept_no, sal, ename)
            SELECT  dept_no, sal, ename
            FROM     emp;
            or maybe:
            INSERT INTO emp_target 
                   (dept_no, sal, ename)
            SELECT  dept_no, sal, ename
            FROM     some_other_table
            WHERE     ename IN (
                                 SELECT  ename
                           FROM    emp
                       );
            You don't need PL/SQL for this.
            Declare
            l_list varchar2(20000);
            LISTAGG will never return more than 4000 characters.
            BEGIN
            select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
            INTO L_LIST
            FROM   emp;
            insert into emp_target(dept_no,sal,ename)
            select dept_no,sal_ename
            where ename in (L_list);
            commit;
            End;
            The sub-query you posted doesn't have a FROM clause, so it's really hard to guess what you want to do.

            Why make people guess anyway? Why not say what you want to do. For example
            "I have these tables: CREATE TABLE ... INSERT ...
            I want to change the emp_target table so that it looks like this ..."

            See the forum FAQ {message:id=9360002}
            • 3. Re: In with lsttagg function
              €$ħ₪
              try this..
              Declare
              l_list varchar2(20000);
              BEGIN
              select trim(LISTAGG(''''||ename||'''', ',') WITHIN GROUP (ORDER BY ename)) AS employees
              INTO L_LIST
              FROM   emp;
              insert into emp_target(dept_no,sal,ename)
              select dept_no,sal_ename
              where ename in (L_list);
              commit;
              End;
              Before you execute add from clause and check your sql ...
              • 4. Re: In with lsttagg function
                JustinCave
                Ummm, have you tried this? If you did, you would find that, in the best case, your `INSERT` would insert 0 rows.

                Justin
                • 5. Re: In with lsttagg function
                  €$ħ₪
                  Yeah i tried this, knew that its not going to work but the original sql missing from clause so just thought of putting "IN" List ..

                  Declare
                  l_list varchar2(20000);
                  BEGIN
                  select trim(LISTAGG(''''||ename||'''', ',') WITHIN GROUP (ORDER BY ename)) into l_list
                  FROM   emp;
                   execute immediate 
                   'insert into emp1(ename)
                  select ename from emp
                  where ename in ('||l_list||')';
                  commit;
                  
                  DBMS_OUTPUT.PUT_LINE(l_list);
                  
                  End;
                  this should work...
                  • 6. Re: In with lsttagg function
                    JustinCave
                    Assuming that you aren't unlucky enough to employ anyone named, say O'Conner, that you run this infrequently enough that hard parsing the query every time won't be problematic, and that there is no risk of someone modifying the data in the EMP table to create a SQL injection attack, then that will work. It will be much less efficient than the other solutions that have been proposed, it'll be harder to maintain and debug in addition to being harder to write. But it should work.

                    Justin