I'm using v7 on OS/390...
I was wondering if this cursor would work..and if only the appropriate sql would be materialized during OPEN or all sqls would be materialized...

Declare cursor x1
case var
when 1 then Select.....(sql1)
when 2 then Select.....(sql2)
when 3 then Select.....(sql3)
end
end-exec

Thanks!!!

What is var?

A host variable?

Can't be a college course...they don't teach mainframe anymore

And yes, everything would be evaluated for the first page of data (I Think..) unless there's a sort, the it would be the entire set (I Think...but only sometimes)

What is var?

A host variable?

Can't be a college course...they don't teach mainframe anymore

And yes, everything would be evaluated for the first page of data (I Think..) unless there's a sort, the it would be the entire set (I Think...but only sometimes)

Also I don't believe that construct would work...you would also need to add FROM sysibm.sysdummy1

Is this a cursor declared within a program? If so, then why not have a separate cursor for each SQL statement and then let the program open the specific cursor based upon a programmatic evaluation of the value of "var"?

yes var will be a host variable.
yes it will be within a program.
The multiple cursors are possible but I would like to minimize the coding.
The actual code would actually result to 17 cursors so it would also need 17 sets of open/fetch/close codes. =(

will this work? note that there is only 1 cursor(x1) but with multiple possible declarations.
exec sql
Case :hostvar
When 1 declare cursor x1
sql1 (select ...from...condition1etc)
When 2 declare cursor x1
sql2 (select ...from...condition2etc)
When 3 declare cursor x1
sql3 (select ...from...condition3etc)
end
end exec

open x1
fetch x1
close x1

Thanks for the replies!!!

I doubt that it will work that way. The "exec sql" statements are commented out in the precompile when the DBRM is created. I don't think that DB2 would like the case statement in the context you have it. You may need to code a separate cursor for each of the 17 sql statements that you have. Are these sql statements all similar?

Minimize coding?

OK, lose the cursors...

Also, if you do try to do it that way, You still have to code the opens and the fetches...which would probably be specific to each cursor anyway...

Can you show us a sample of what one cursor is and what it's to be used for?

Are you reading QSAM Files?

The sql statements are similar in the sense that the selected columns and some of the critierias are the same.

if different cursors...
cursor1 -> valid only if hostvar=x
select a,b,c
from taba
where d >= :varmin
and d <= :varmax
and ....(common conditions)


cursor2 -> valid only if hostvar=y
select a,b,c
from taba
where e >= :varmin
and e <= :varmax
and ....(common conditions)


cursor3 -> valid only if hostvar=z
select a,b,c
from taba
where f >= :varmin
and f <= :varmax
and ....(common conditions)


Since the selected columns and program logic for openning and retrieving records are the same, I thought I could minimize coding by using only 1 set of open/fetch/close.

The "declare cursor" statement is not actually executable code. In fact, it should be placed in the Working Storage section of the program and not in the Procedure Division (although it will work in any part of the program so long as it physically resides before the open cursor statement). Therefore the declare cursor itself never does anything, and never retrieves any (or all) rows.

The Open Cusor statement (which is really what invokes the declared cursor) can be in a conditional statement. Whether the cursor is materialized when the open is executed (or when the first fetch is executed) depends on several factors such as whether DB2 has to retrive all rows and sort them in order to return the first row to the program when a fetch occurs. In the mainframe, DB2 will usually not actually do anything until the first fetch is executed.