union in stored procedure

wishlister · #1 (**permalink**) 03-08-07, 12:04

how will i perform UNION of SELECT statements in stored procedure?

sathyaram_s · #2 (**permalink**) 03-08-07, 12:18

Use the UNION Keyword as you would use in SQL

Sathyaram

PS: Not sure if the response helps you ... But, with the info you have given, am unable to give any better response

wishlister · #3 (**permalink**) 03-08-07, 12:29

it didn;t work maybe because there is FOR before my select statement. can u give me an example for this.. union of 2 statements with cursor also! tnx

sathyaram_s · #4 (**permalink**) 03-08-07, 12:32

It will be more useful if you can provide an example of what you are looking for ...

Also, give your OS, db2 version informaions

Sathyaram

wishlister · #5 (**permalink**) 03-08-07, 12:42

db2 ESE

for
SELECT <statement>;
OPEN Cursor1;
FETCH <statement>;
WHILE SQLSTATE = '00000' Do
INSERT INTO <statement>;
FETCH <statement>;
END WHILE;
close Cursor1;
UNION
for
for
SELECT <statement>;
OPEN Cursor2;
FETCH <statement>;
WHILE SQLSTATE = '00000' Do
INSERT INTO <statement>;
FETCH <statement>;
END WHILE;
close Cursor2;

//An unexpected token "for" was found

wishlister · #6 (**permalink**) 03-08-07, 12:48

another question..
cursor1 is for the first table and cursor2 for the second table. is it right to use different cursors? or can i use only one cursor?

stolze · #7 (**permalink**) 03-09-07, 04:08

A union combines two subselects into one. So you would have:

Code:

DECLARE ... CURSOR FOR
   SELECT ... FROM ... WHERE ...
   UNION
   SELECT ... FROM ... WHERE ...;
OPEN ...;
WHILE ( ... ) DO
   FETCH ...
END WHILE;
CLOSE ...;

Internet Services

Web Development

Digital Marketing

Consumer Tech