Home » Developer & Programmer » Forms » dynamic IN operator values (Dev 6i, 10g Rel 1 and 2)
dynamic IN operator values [message #331004] Wed, 02 July 2008 02:11 Go to next message
oralover
Messages: 97
Registered: January 2005
Member
hi all!

I want to execute a SQL statement in which i am using IN operator, but want to input from user for 4 fields and check whether user entered all 4 values or less ( 1 or 2 or 3) and a variable will store these values and after IN operator i want to enter this variable and execute SQL statement. I tried but failed.

declare
  for_jobs varchar2(100);
begin
for_jobs := rtrim(:first_job);
for_jobs := for_jobs||rtrim(:sec_job);
for_jobs := for_jobs||rtrim(:third_job);
for_jobs := for_jobs||rtrim(:forth_job);
-- 
:disp_for_jobs := for_jobs;
-- 
for rec in (select empno, ename, job, deptno
              from emp
             where job in (for_jobs)
             order by 3,1 
            )
loop
	 message (rec.empno||', '||rec.ename||', '||rec.job||', '||rec.deptno);
	 pause;
end loop;
end;


can it be done like this or any other way...???
kindly help.
thanks in advance

[Updated on: Wed, 02 July 2008 02:23]

Report message to a moderator

Re: dynamic IN operator values [message #331106 is a reply to message #331004] Wed, 02 July 2008 05:47 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi
Try like below


for rec in (select empno, ename, job, deptno
              from emp
       where job in (rtrim(:first_job),rtrim(:sec_job).....)
             order by 3,1 
            )



kanish
Re: dynamic IN operator values [message #331129 is a reply to message #331004] Wed, 02 July 2008 05:59 Go to previous message
oralover
Messages: 97
Registered: January 2005
Member
Thanks very much mm_kanish05 for help.

SOLVED
Previous Topic: VB Style Input Feeding.
Next Topic: forms 4.5 / oracle 9i
Goto Forum:
  


Current Time: Sun Dec 04 22:40:47 CST 2016

Total time taken to generate the page: 0.15418 seconds