Page 1 of 1

converting dynamic cursor into static cursor

PostPosted: Mon Jun 17, 2013 5:36 pm
by rupa888
We have a requirement in our project to convert a dynamic cursor to a static cursor.

In the dynamic cursor, we have used 3 dynamic statements in the WHERE condition.

Say, the 3 fields are INP-A,INP-B,INP-C. INP-A, INP-B and INP-C are passed from the user.

IF INP-A and INP-B has values and INP-C is SPACES, then my where clause will be

Select -----
from table
where A = :WS-A
AND B = :WS-B

or IF INP-A has values and INP-B and INP-C is SPACES, then my where clause will be

Select -----
from table
where A = :WS-A

and so on.

We need to convert such a dynamic cursor into a static cursor. There is one possible solution that we tried - where 1 dynamic cursor is converted into N static cursors ( In the above example, N = 8 )

We require a solution where 1 dynamic cursor is converted to 1 static cursor.

Can you please help me out with a possible solution?

Re: converting dynamic cursor into static cursor

PostPosted: Mon Jun 17, 2013 7:12 pm
by dick scherrer
Hello,

We have a requirement in our project to convert a dynamic cursor to a static cursor.
Why is this now a requirement?

Re: converting dynamic cursor into static cursor

PostPosted: Tue Jun 18, 2013 10:55 am
by rupa888
@dick...

We have some old programs which were coded many years back. One requirement was to remove these dynamic cursors and replace it with static ones. Apart from the solution to convert into N static cursors based on the input condition, can you suggest anything to achieve the same result using a single static cursor from a single dynamic one.

Re: converting dynamic cursor into static cursor

PostPosted: Tue Jun 18, 2013 8:37 pm
by dick scherrer
Hello,

I can't think of another way - if i do, i'll post more here.

I still do not understand why this is being done . . . Is there some business or technical reason or is this because "someone wants to"?

Re: converting dynamic cursor into static cursor

PostPosted: Wed Jun 19, 2013 10:48 am
by rupa888
hi dick,
We found out the solution for this.the dynamic query can be converted into static query as follows:

select *
from table name
where (inp_ a = :hostvariable.inp-a or :hostvariable.inp-a = ' ')
and (inp_ b = :hostvariable.inp-b or :hostvariable.inp-b = ' ')
and (inp_ c = :hostvariable.inp-c or :hostvariable.inp-c = ' ')

Re: converting dynamic cursor into static cursor

PostPosted: Wed Jun 19, 2013 6:43 pm
by dick scherrer
Hello,

That will run, but i wonder about performance . . . Suggest you do Thorough testing that this will work satisfactorily on queries with high volumes of data to look at to satisfy the query.

But i still do not understand Why this is being done . . .