Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    42

    Unanswered: union in stored procedure

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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2007
    Posts
    42
    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2007
    Posts
    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

  6. #6
    Join Date
    Feb 2007
    Posts
    42
    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?

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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 ...;
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •