how to use IN clause in the report query

[Iloveoracle]

Hi,

I designed one simple tabular form report.It will take list of values and displays their details.
For example we will consider emp table,my sql query is
"select * from emp where empno in ('100E','1002E',
'1003M','1004S','1005H')"
This query will retrive all details basing on the IN clause.Coming to the report,I wrote query like this
"select * from emp where &p_empno"When I run this report in paper layout it will take values with single codes like
('100E','1002E','1003M','1004S','1005H') and it displays details basing on this list.
But If want to run through URL how can I pass these single coded values to the report.

Any assistance will be appreciated.
Thanks in advance
GP

 

[wtchout]

first you would need to create a function that will break up the string to individual records into a user defined type.

so what would happen is

select * from emp where empno in (select column_value from the (select cast (ParseStringToTableemplist,',') as StringTable from dual))

emplist variable value will be '100E,1002E,1003M,1004S,1005H'

you will need to create the ParseStringToTable Function as below

CREATE OR REPLACE FUNCTION TOM.parsestringtotable (
inlist VARCHAR2,
delimiter VARCHAR2
)
RETURN matheson_string_table
AS
-- define maximum varchar2 field
clist VARCHAR2 (32767) DEFAULT inlist;
ipos NUMBER;
rtntable StringTable := StringTable ();
BEGIN
-- jae: 4/21/2006 10:47 am
-- generic parsing routine, should replace varchartotable, stringToTable functions
-- as this will pass a delimiter
IF (SUBSTR (clist, LENGTH (clist), 1) <> delimiter)
THEN
clist := clist || delimiter;
END IF;

LOOP
ipos := INSTR (clist, delimiter);
-- exit if not found
EXIT WHEN (NVL (ipos, 0) = 0);
-- add another element to the table
rtntable.EXTEND;
-- add value to the table
rtntable (rtntable.COUNT) :=
LTRIM (RTRIM (SUBSTR (clist, 1, ipos - 1)));
-- update cList variable with next inlist until no more
clist := SUBSTR (clist, ipos + 1);
END LOOP;

RETURN rtntable;
END parsestringtotable;
/

then create the User Defined Type
CREATE OR REPLACE
TYPE STRINGTABLE AS TABLE OF VARCHAR2(100)
/

hth
joel

 

[nettytramp]

Hi,
I am not sure what you meant by running the report through URL. I am assuming that you have a web application and are using Oracle reports or you are running the report through Oracle Applications.

I would sugges that instead of using th quotes, you can use replace command to replace ',' with '',' (quote and a comma).

I have used it and it worked for me. I think you can do the same. I have a trigger in after parameter form.
Below is the code
-----------------------
If instrP_empno, ',')>0 then
_empno := ' and emp.empno in ( ''' || replaceP_empno, ',' , ''',''') || ''')';
elsif _empno is null then
_empno := ' and 1 = 1 ';
else
_empno := ' and emp.empno in (''' || _empno ||''' )';
end if;

srw.message(1,_empno);
----------------
Hope this helps.

Kiran