Re: The usage of UNION in a curosor for Pro*C programs

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 04 Sep 1998 14:48:44 GMT
Message-ID: <361bfd40.179634240@192.86.155.100>

A copy of this was sent to "DES1" <des1_at_leading.net> (if that email address didn't require changing) On Fri, 4 Sep 1998 10:11:02 -0400, you wrote:

>Hi,
>I am trying to use the UNION in a CURSOR declaration. It compiles fine and
>when we run the program it fetches only the rows corresponding to the first
>query in the cursor statement. We do not get any rows corresponding to the
>second query. But we know that second query can fetch some rows if executed
>seperately. We checked in the ORACLE manuals and we could not find any
>specific ways of using unions in the cursors declarations.
>
>Any help on this greatly appreciated.
>
>Viren & Raj
>email: des1_at_southeast.net
>BCBSF
>
>

Are you sure that you aren't just observing the expected outcome of a UNION (vs a UNION ALL).

(query a) UNION (query b) is the same as

(query a) PLUS (query b MINUS query a)

A union of 2 sets is everything from the first set PLUS everything from the second set that is not in the first set.

UNION ALL is (query a) PLUS ( query b )

For example, given the following pro*c snippet:

void process(void )
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR str[512];
EXEC SQL END DECLARE SECTION;     EXEC SQL DECLARE C1 CURSOR FOR

      SELECT DUMMY FROM DUAL
      UNION
      SELECT DUMMY FROM DUAL
      UNION
      SELECT DUMMY FROM DUAL;

    EXEC SQL DECLARE C2 CURSOR FOR
      SELECT DUMMY FROM DUAL
      UNION ALL
      SELECT DUMMY FROM DUAL
      UNION ALL
      SELECT DUMMY FROM DUAL;

    EXEC SQL WHENEVER NOTFOUND DO break;

    printf( "Cursor 1 with UNION\n" );
    EXEC SQL OPEN C1;
    for(;;)
    {

        EXEC SQL FETCH C1 INTO :str;
        printf( "%.*s\n", str.len, str.arr );

    }

    printf( "Cursor 2 with UNION ALL\n" );     EXEC SQL OPEN C2;
    for(;;)
    {

        EXEC SQL FETCH C2 INTO :str;
        printf( "%.*s\n", str.len, str.arr );

    }
}

The output will be:

$ ./test
Cursor 1 with UNION
X
Cursor 2 with UNION ALL
X
X
X

The only difference between the 2 is that cursor C2 uses UNION ALL, not UNION....   Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Sep 04 1998 - 09:48:44 CDT