| cursor [message #310540] |
Tue, 01 April 2008 09:45  |
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 #310717 is a reply to message #310571] |
Wed, 02 April 2008 03:30  |
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 |
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 |
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 |
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 |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
