Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to use IN clause in the report query

Status
Not open for further replies.

Iloveoracle

Programmer
Oct 2, 2006
22
US
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

 
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 (ParseStringToTable:)emplist,',') 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
 
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 instr:)P_empno, ',')>0 then
:p_empno := ' and emp.empno in ( ''' || replace:)P_empno, ',' , ''',''') || ''')';
elsif :p_empno is null then
:p_empno := ' and 1 = 1 ';
else
:p_empno := ' and emp.empno in (''' || :p_empno ||''' )';
end if;

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

Kiran
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top