Want to declare 3 cursors for 3 conditions

ashimer · Posted: Tue Nov 28, 2006 1:46 pm

i have a cursor declared as
declare c1 cursor for
select name,empno,empdept
from table
where
name = :ws-name
and empno =:ws-empno
and dept = :ws-dept

now depending on an input condition the where clause will change ...it can use 3 keys ....if 3 keys are thr in input or else 1st and 3rd or 2 nd and 3rd

i dont want to declare 3 cursors for 3 conditions ....
if only name and empno is thr in input and thr is no value for dept the where clause shud have only name and empno ...

is thr any way out of this ...
if i can use dynamic sql how do i declare it .....open and fetch ....
pls help

UmeySan · Posted: Tue Nov 28, 2006 5:41 pm

Hi !

For a quick and easy entry in DynamicSQL please look at www.craigsmullins.com/dbu_1004.htm.

But remenber that dynamic sql is increasing performance !!!

In a lot of companies with the need of high performance applications it's forbidden to use dynamic sql.

So just make it easy. Define three cursors and a few minutes later, your work is done.

Regards, UmeySan

ashimer · Posted: Thu Nov 30, 2006 11:29 am

thanks umey for ur response ..... but here the above cursor was just an example .....actually the where clause contains 6 keys ..and in total i shud declare 18 cursors which is goin to give a huge bind ....
neways i figured out a way for this ...
u can declare the cursor as below ...
declare c1 cursor for
select name,empno,empdept
from table
where
( name = :ws-name or name = ' ')
and ( empno =:ws-empno or empno = ' ' )
and ( dept = :ws-dept or dept = ' ')

this worked fine .........