Re: Difficult SQL Query

From: Chinna <jchinna_at_enron.com>
Date: 1997/09/23
Message-ID: <01bcc866$66f9d640$06f7fb9b_at_eoghou1325.eog.enron.com>#1/1


Try :

select * from table2
minus
select * from table1;

Let me know if this helped.

Chinna

Gary England <gengland_at_hiwaay.net> wrote in article <3421D560.5E31_at_hiwaay.net>...
> James Bourne wrote:
> >
> > All,
> >
> > I am having a problem getting a query to work:
> >
> > I have two tables where I want to find the difference
> > between the two:
> >
> > Table1
> > Attribute1 Number(8)
> >
> > Table2
> > Attribute1 Number(8)
> >
> > I want to select all Table1.Attribute1 where
> > Table1.Attribute does not exist in Table2
> >
> > How can I do this?
>
> I have found the fastest way to get this result is
>
> SELECT ... FROM Table1, Table2
> WHERE Table1.Attribute1 = Table2.Attribute1 (+)
> AND Table1.Attribute1 IS NULL;
>
> The outer join will use the indexes (assuming Attribute1 i indexed in
> Table2) and fill Table2 will null values. You just pick the rows where
> they are null. Fast and easy.
>
> >
> > Secondly, I am setting up a cursor in Pro*C and
> > whenever not more records are found I want to return
> > a failure value.
> >
> > Example:
> >
> > EXEC SQL WHENEVER NOT FOUND DO return value;
> >
> > The Pro*C compiler (2.2.2.0.0 on Solaris 7.3.2) keeps on
> > complaining!
> >
>
> The WHENEVER construct wants to go somewhere. If you are within a
> sub-function the stack may not be positioned for the "return value" to
> get you where you want to go.
>
> Turn on the Verbose option of Pro*C and check the intermediate code.
>
> Good luck,
> Gary
>
  Received on Tue Sep 23 1997 - 00:00:00 CEST

Original text of this message