Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #183804] Sun, 23 July 2006 23:33 Go to next message
indraneelkumar
Messages: 24
Registered: September 2005
Location: bangalore
Junior Member

   Hi,
   I need one query.
    my requirement is as follows
    I query am passing some parameters.
    In run time depending on parameter value records should 
    return.
    Ex: Take EMP Table
       If the parameter value is 1 then 
        the query should return the records constain 
       deptno as 10.
       If the parameter value is 2 then
        the query should return the records constain
        detpno as 20,30.
       I tried as follows. But no luck.

      select * 
      from emp 
      where to_char(deptno) in 
                  (select decode(:para_1,1,'10',2,'20,30')
                   from   dual)
      here if the value of :para_1 is 1 then it is 
      returning the records for the corresponding deptno (10)
   
      if the values of :para_1 is 2 then the value'20,30' 
      is returning as string. So am not able to get the 
      records for the deptno 20,30.

      Plz help me

Thanks in advance
Re: sql query [message #183824 is a reply to message #183804] Mon, 24 July 2006 01:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


A comma-separated string of numbers is not an array.

Look for the reason for the erros


http://www.williamrobertson.net/documents/comma_separated.html


One option is like this .. (pipelined function)

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.

SQL>


Thumbs Up
Rajuvan.











[Updated on: Mon, 24 July 2006 01:31]

Report message to a moderator

Re: sql query [message #183829 is a reply to message #183804] Mon, 24 July 2006 01:37 Go to previous messageGo to next message
rajaorafaq
Messages: 18
Registered: August 2005
Location: Newyork
Junior Member

Try this

select *
from emp
where deptno in
(select decode(:para_1,1,10,2,20,30,3,30)
from dual)

regards

Rajagopal
Re: sql query [message #183837 is a reply to message #183824] Mon, 24 July 2006 02:08 Go to previous messageGo to next message
indraneelkumar
Messages: 24
Registered: September 2005
Location: bangalore
Junior Member

Thanks rajavvi
Re: sql query [message #183846 is a reply to message #183829] Mon, 24 July 2006 02:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmmm.... I don't think you tested that, did you. I base this on my observation that wen I run your query for a parameter value of 2, I only get the records back for Dept 20

SQL> select ename,empno,deptno from temp_emp;

ENAME                               EMPNO     DEPTNO
------------------------------ ---------- ----------
KING                                 1000         10
SMITH                                2000         20
JONES                                3000         20
BLOGGS                               4000         30
HARCOURT                             5000         30

SQL> select ename,empno,deptno 
  2  from temp_emp 
  3  where deptno in 
  4  (select decode(2,1,10,2,20,30,3,30)
  5  from dual);

ENAME                               EMPNO     DEPTNO
------------------------------ ---------- ----------
SMITH                                2000         20
JONES                                3000         20
Previous Topic: data_length in user_tab_columns
Next Topic: DBMS_APPLICATION_INFO.SET_MODULE
Goto Forum:
  


Current Time: Mon Dec 05 23:54:17 CST 2016

Total time taken to generate the page: 0.04886 seconds