1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

multiple variables

Discussion in 'SQL PL/SQL' started by stefanp50, Dec 17, 2014.

  1. stefanp50

    stefanp50 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    how to modify:
    And Rqmt.Ord_nbr = &<NAME=Order_No TYPE = "STRING" >
    to enter multiple variables?
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    What you are asking is not made clear in the post. You want to be able to compare RGMT.ORD_NBR to multiple values? If so, you would use an IN statement.
     
  3. stefanp50

    stefanp50 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Matthew,
    I don't have much experience (at all) with this but was aware of the IN option. Thought that there was a more "elegant" way of doing it. Maybe not?
    Stefan
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Define 'elegant' in that context. IN is the 'elegant' way to write such a comparison; the alternative is a long series of OR conditions. For example:


    Code (SQL):

    SQL> SELECT *
      2  FROM emp
      3  WHERE empno IN (7369,7499,7566,7654)
      4  /


         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
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30


    SQL>
    SQL> SELECT *
      2  FROM emp
      3  WHERE empno = 7369
      4  OR empno = 7499
      5  OR empno = 7566
      6  OR empno = 7654
      7  /


         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
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30


    SQL>
     

    How much more 'elegant' can IN be?


    But that's not what you really want, is it? From your sparse example it appears you have multiple columns you are trying to match so IN isn't the tool for that job. In that case you have no other choice than to use AND to build a more complex predicate:


    Code (SQL):

    SQL> SELECT *
      2  FROM emp
      3  WHERE empno = 7369
      4  AND job = 'CLERK'
      5  /


         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

          7369 SMITH      CLERK           7902 17-DEC-80        800                    20


    SQL>
    SQL> SELECT *
      2  FROM emp
      3  WHERE empno = 7499
      4  AND job = 'CLERK'
      5  /


    no ROWS selected


    SQL>
    SQL> SELECT *
      2  FROM emp
      3  WHERE empno = 7499
      4  AND job = 'SALESMAN'
      5  /


         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30


    SQL>
     

    And there is no other, more 'elegant' way to do that.
     
    stefanp50 likes this.