Re: Difficult SQL Query

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 Thu Sep 18 1997 - 00:00:00 CDT