Home » SQL & PL/SQL » SQL & PL/SQL » Decode with multiple values
Decode with multiple values [message #183960] Mon, 24 July 2006 09:51 Go to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
My requirement is I need to select 1 or 2 or 3 into a variable.

If 0 is selected, then variable should have all the values 1,2,3.

otherwise selected value of 1 or 2 or 3 should be taken.

First time the variable will have NULL. Using NVL 0 is passed into variable.


Using DECODE,I am able to select only one value into variable. How can i select multiple values into variable.

Can any one help in writing SQL query for this!!

Thanks in advance
HK
Re: Decode with multiple values [message #183961 is a reply to message #183960] Mon, 24 July 2006 09:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't select multiple values into a variable, unless that variable is some sort of collection.

If I said 'You're planning to try and use this variable as part of an IN statement' would I be far from the truth?
Re: Decode with multiple values [message #184031 is a reply to message #183960] Mon, 24 July 2006 23:26 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Is this what you are looking for ??

SQL> drop type  array1 ;

Type dropped.

SQL> CREATE TYPE array1 AS TABLE OF NUMBER
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION ret_array(str VARCHAR2 )
  2  RETURN ARRAY1 PIPELINED
  3  IS
  4  str1  VARCHAR2(100);
  5  num1  NUMBER(5);
  6  BEGIN
  7  str1 := str ||',';
  8  WHILE LENGTH(str1)>=0
  9  LOOP
 10       num1 := TO_NUMBER(SUBSTR(str1,1,INSTR(str1,',',1)-1));
 11       pipe ROW (num1);
 12       str1 := SUBSTR(str1,INSTR(str1,',',1)+1);
 13  END LOOP;
 14  --NULL;
 15  RETURN  ;
 16  END;
 17  /

Function created.

SQL> select EMPNO, ENAME, JOB,DEPTNO
  2  FROM EMP
  3  WHERE deptno IN
  4       (SELECT COLUMN_VALUE FROM
  5        TABLE(ret_array(DECODE(&para,1,'10',2,'20,30'))));
Enter value for para: 1
old   5:       TABLE(ret_array(DECODE(&para,1,'10',2,'20,30'))))
new   5:       TABLE(ret_array(DECODE(1,1,'10',2,'20,30'))))

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10

SQL> /
Enter value for para: 2
old   5:       TABLE(ret_array(DECODE(&para,1,'10',2,'20,30'))))
new   5:       TABLE(ret_array(DECODE(2,1,'10',2,'20,30'))))

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7566 JONES      MANAGER           20
      7902 FORD       ANALYST           20
      7876 ADAMS      CLERK             20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7900 JAMES      CLERK             30
      7844 TURNER     SALESMAN          30
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7521 WARD       SALESMAN          30

11 rows selected.


Thumbs Up
Rajuvan.
Previous Topic: select statement
Next Topic: add value to one record based on another record
Goto Forum:
  


Current Time: Mon Dec 05 18:51:32 CST 2016

Total time taken to generate the page: 0.11766 seconds