7 Replies Latest reply on Dec 27, 2012 2:11 PM by 968376

    Dynamically Entering two or more values!!!

    968376
      Hello Team,

      I need a sql query to enter dynamically two or more values for evaluating with in that range at run time .

      Eg: I need to know the employee details for which the deptno is 10,20. So I need to enter 10,20 or more at run time.Suppose If I enter 10,20 values for the deptno.
      Select * from emp where deptno in (10,20) if we enter (10,20)
      Select * from emp where deptno in (10,20,30) if we enter (10,20,30);

      So I need a query to build such type of requests.

      Thanks in Advance!!!
        • 1. Re: Dynamically Entering two or more values!!!
          Karthick2003
          Like this?
          SQL> select * from emp;
           
               EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO
          ---------- ------ --------- ---------- --------- ---------- ---------- ----------
                7369 SMITH  CLERK           7902 17-DEC-80        800          0         20
                7499 ALLEN  SALESMAN        7698 20-FEB-81       1600        300         30
                7521 WARD   SALESMAN        7698 22-FEB-81       1250        500         30
                7566 JONES  MANAGER         7839 02-APR-81       2975          0         20
                7654 MARTIN SALESMAN        7698 28-SEP-81       1250       1400         30
                7698 BLAKE  MANAGER         7839 01-MAY-81       2850          0         30
                7782 CLARK  MANAGER         7839 09-JUN-81       2450          0         10
                7788 SCOTT  ANALYST         7566 19-APR-87       3000          0         20
                7839 KING   PRESIDENT            17-NOV-81       5000          0         10
                7844 TURNER SALESMAN        7698 08-SEP-81       1500          0         30
                7876 ADAMS  CLERK           7788 23-MAY-87       1100          0         20
           
          11 rows selected.
           
          SQL> var emp_no varchar2(100)
          SQL> 
          SQL> exec :emp_no := '7369,7499,7521,7566'
           
          PL/SQL procedure successfully completed.
           
          SQL> select *
            2     from emp
            3    where ',' || :emp_no || ',' like '%,' || empno || ',%'
            4  /
           
               EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO
          ---------- ------ --------- ---------- --------- ---------- ---------- ----------
                7369 SMITH  CLERK           7902 17-DEC-80        800          0         20
                7499 ALLEN  SALESMAN        7698 20-FEB-81       1600        300         30
                7521 WARD   SALESMAN        7698 22-FEB-81       1250        500         30
                7566 JONES  MANAGER         7839 02-APR-81       2975          0         20
           
          SQL> 
          • 2. Re: Dynamically Entering two or more values!!!
            968376
            Thanks for your reply.


            not like that.. I told you that!! suppose I need to get the details of deptno 10, 20, 30. I will enter only 10, 20 ,30 correspondingly I need to get the details of employee.
            I can have the facility to change the values dynamically, once 10,20 another 10,20,30 another time 10,20,30,40.

            Query you provided one is restricted to only certain values.. not like that I want.
            • 3. Re: Dynamically Entering two or more values!!!
              968376
              Generally for retrieving data from multiple set of values we will use IN right.

              Eg: select * from emp where deptno IN (10,20,30);

              I need here to enter 10,20,30 dynamically. not 10,20,30 i Can have my own set of values to be searched and retrieved.
              • 4. Re: Dynamically Entering two or more values!!!
                Solomon Yakobson
                What tool are you using? Assuming it is SQL*Plus:
                SQL> accept dept_list prompt "Enter department list: "
                Enter department list: 10,20
                SQL> select  *
                  2    from emp
                  3    where deptno in (&dept_list)
                  4  /
                old   3:   where deptno in (&dept_list)
                new   3:   where deptno in (10,20)
                
                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                      7839 KING       PRESIDENT            17-NOV-81       5000                    10
                      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                
                8 rows selected.
                
                SQL> accept dept_list prompt "Enter department list: "
                Enter department list: 10,20,30
                SQL> select  *
                  2    from emp
                  3    where deptno in (&dept_list)
                  4  /
                old   3:   where deptno in (&dept_list)
                new   3:   where deptno in (10,20,30)
                
                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
                      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                      7839 KING       PRESIDENT            17-NOV-81       5000                    10
                      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                
                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                
                14 rows selected.
                
                SQL> 
                SY.
                • 5. Re: Dynamically Entering two or more values!!!
                  968376
                  I am using SQL*Plus..

                  Here it looks almost the same. But why we are entering the values at the first only.. First you are entering the values, after that query.

                  I can find one disadvantage here. We need to write that two statements every time.
                  1) accept statement
                  2) Following statement.

                  Can we have a query simply like we will enter the values, after entering values we need to retrieve the data.

                  Eg: Analyse the output:

                  Enter value for deptno: 10,20

                  OUTPUT:

                  --------
                  -------

                  /

                  Enter value for deptno: 10,20,30
                  OUTPUT:

                  --------
                  -------

                  /

                  Enter value for deptno: 10,20,30,40
                  OUTPUT:

                  --------
                  -------

                  /

                  I need this type of query.
                  • 6. Re: Dynamically Entering two or more values!!!
                    Solomon Yakobson
                    965373 wrote:
                    I can find one disadvantage here. We need to write that two statements every time.
                    1) accept statement
                    2) Following statement.
                    Not true and you could have tested it yourself:
                    SQL> set verify off
                    SQL> accept dept_list prompt "Enter department list: "
                    Enter department list: 10,20
                    SQL> select  *
                      2    from emp
                      3    where deptno in (&dept_list)
                      4  /
                    
                         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                          7839 KING       PRESIDENT            17-NOV-81       5000                    10
                          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                    
                    8 rows selected.
                    
                    SQL> accept dept_list prompt "Enter department list: "
                    Enter department list: 10,20,30
                    SQL> /
                    
                         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
                          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                          7839 KING       PRESIDENT            17-NOV-81       5000                    10
                          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                    
                         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                    
                    14 rows selected.
                    
                    SQL> accept dept_list prompt "Enter department list: "
                    Enter department list: 10,20,30,40
                    SQL> /
                    
                         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
                          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                          7839 KING       PRESIDENT            17-NOV-81       5000                    10
                          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                    
                         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                    
                    14 rows selected.
                    
                    SQL> 
                    SY.
                    • 7. Re: Dynamically Entering two or more values!!!
                      968376
                      It looks perfect now...!!!

                      Thanks for your valuable replies and support..!!!:)