Dear Sir,

I want to know that cursor can join with table in the same sql ? Sample as below.

declare c_1 cursor with hold for
select case_num, amount from table1;

declare c2 cursor with hold for
select a.name, b.case_num, b.amount from table2 a, c_1 b where a.name = b.name (+);


Thanks !

as long as the select is valid, it can also be defined for a cursor..

amcc38,

Why do you want to do such troublesome/useless way?
It is not necessary to delare c_1.
You can join table1 with table2 in c_2.

This syntax is valid only in Oracle or (may be valid) in DB2 setting compatibility vector to Oracle.
Use of newer syntax using OUTER JOIN keyword is recommended.

Actually, the c_1 will be used as master table and it will be joined with other tables and form several cursor for further use. If c_1 extraction logic has been changes, needn't to modify each cursor. Logically, I think it works, right ?

Anyway, thanks !

Usually, using multiple cursor is a sign of bad design of DB access(with SQL).
I think if you showed whole procedure using these cursors,
I could show you the simplified design with reducing number of cursors(usually to one cursor).

Anyway, you can go your way.

No, this won't work in DB2. The "(+)" notation is (legacy) Oracle notation, not standard SQL. Do rather something like:
Moreover, this cursor makes cursor c1 unnecessary: it returns exactly the same results in the first two columns as c1 (be it possibly repeatedly, and/or both NULL).