Home » Developer & Programmer » Forms » cursor (Forms 6i)
cursor [message #310540] Tue, 01 April 2008 09:45 Go to next message
dillango
Messages: 145
Registered: January 2008
Senior Member
Dear All,

I am displaying multiple records by fetching from base table records through cursor (depends on the user input).

Say for example, user can choose one branch, full company or one department etc..

Depends on the input, I have to create cursor but the problem is I cannot check "If" condition in declaration part.

declare
If :branch is not null then
cursor c1 is select empno from emp where branch = :branch;
elsif :dep is not null then
cursor c1 is select empno from emp where dep = :dep;
end if;
begin
for s1 in c1 loop
:empno := s1.empno;
next_record;
end loop;
end;

My purpose is, I want to avoid for..loop multiple times.

Any simple idea please?

Ilango
Re: cursor [message #310571 is a reply to message #310540] Tue, 01 April 2008 12:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Can't it be done in any easier way?
Try using default where clause property at run time in pre-query trigger.

By
Vamsi
Re: cursor [message #310717 is a reply to message #310571] Wed, 02 April 2008 03:30 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
declare
cursor c1 is select empno from emp where 
     branch = nvl(:branch,branch)
     and dep = nvl(:dep,dep);

begin
for s1 in c1 loop
:empno := s1.empno;
next_record;
end loop;
end;
Re: cursor [message #310726 is a reply to message #310717] Wed, 02 April 2008 04:03 Go to previous messageGo to next message
dillango
Messages: 145
Registered: January 2008
Senior Member
Thank Mr.Vamsi,

However, where I can use the Pre-query trigger?. After collecting all the information from the header block I am creating this cursor just before enter into datbase block.
For time being I managed like this.

If :branch is not null then
declare
cursor c1 is select ... where brn = :branch;
begin
...
...
end;
elsif :department is not null then
declare
cursor c1 is select ... where dep = :department;
begin
...
...
end;
...
...

Thank you once again.
Ilango
Re: cursor [message #310768 is a reply to message #310726] Wed, 02 April 2008 06:26 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Dear,
You can create three cursors for all three values in declare
section and open the particular cursor in begin section for which the value is entered by the user.

ok bye

Athar
Re: cursor [message #310786 is a reply to message #310768] Wed, 02 April 2008 07:26 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
i wonder u can achiieve all u want in just cursor using NVL function , dont know why are you so confused.
Re: cursor [message #310855 is a reply to message #310786] Wed, 02 April 2008 11:26 Go to previous message
dillango
Messages: 145
Registered: January 2008
Senior Member
Mr.Sispk6,

Your cursor wont work..
Please check your below query sent me before.
The user will choose either Branch or Department and not both.
So, how your query will fetch the required information.

However, Mr.Athar suggestion will work.
Any way thank you for your suggestion.
Ilango



declare
cursor c1 is select empno from emp where
branch = nvl(:branch,branch)
and dep = nvl(:dep,dep);

begin
for s1 in c1 loop
:empno := s1.empno;
next_record;
end loop;
end;
Previous Topic: Read user input value when a button is pressed
Next Topic: report generation shows ora00942-table or view does not exist
Goto Forum:
  


Current Time: Tue Dec 06 10:21:48 CST 2016

Total time taken to generate the page: 0.08421 seconds