I want to qualify date1 from a date field from of table1 and then use the date1 to qualify a second select statment on table2. I'm confused on how to handle the declare statment and then open the cursor in the second statement. any help would be appreicated. thanks.

my example is

@mydate1 = select date1 from table1
select * from table2 where date2 = @mydate1

select * from table2 where date2 = (select date1 from table1)

If there is more than value retrieved (even if the multiple values are equal) in the subselect then change the = to IN. Or use a select distinct if the multiple values are equal in the subselect.

yes I understand the subselect statment works the same way,
but that's not what I'm looking for, I'm looking for the right way to declare and the then reference a cursor.
thanks

That depends entirely on what programming language or procedure you are using. You want to store the output of a query in a host variale and then use the host variable in the next query.

I guess what I'm looking to do is come up with a type of session or temporary variable that can be altered based on what sepcific time period the users are looking for. for the end users who are writing sql against db2 on unix.
by declaring or creating a variable/Alias upfront and then referencing the vairable thru out the code, I'm hoping to gain a better access paths against the database server instead of joining to multiple tables.

Are you wanting to use the sql in a unix script, C program, stored procedure, etc? Please explain. It makes a difference.

the end users typicaly use business object to build and automate their reports.

So, it's either ODBC or JDBC... I guess you can't do what you want in plain SQL. You could create a stored proc that returns a resultset to the caller and pass the date to that stored proc; however I'm not sure BusinessObjects can invoke stored procedures...